BizTalk Server database databases and their health are very important for a successful BizTalk Server database messaging environment. This is nothing new and everybody knows!
Although there can be many settings that we can configure, like auto-growth settings for BizTalk Databases (you can learn more here), there are two main things that we must understand and be aware, especially the database administrators:
Execution of the BizTalk Server SQL Agent jobs are crucial for managing the BizTalk Server databases and for maintaining optimal performance.
The Backup BizTalk Server job is the only supported method to backup the BizTalk Server databases and requires that all of the BizTalk Server databases are configured to use the SQL Server full recovery model.
BizTalk Server 2010/2013 is shipped out with a total of 13 SQL Agent jobs. 2 of these jobs must be configured. The two jobs that needs configuration are the two most important jobs:
The “Backup BizTalk Server“: This is the job provided by Microsoft to do a best practice backup of the BizTalk databases.
And the “DTA Purge and Archive“: This SQL Agent job purges and archives information from the tracking database, you do need to configure this job in order for it to work.
However what many times we forget is that this two jobs, by default, don’t provide functionalities for deleting backup files that have accumulated over time on our file system and we normally forget to create a “process” or a “job” to accomplish this until is too late. The result of that is… lots of times we just remember when disks are full and everything stop to work!
We can for example implementing your custom “sp_ DeleteBackupHistoryAndFiles” as you can see in my post: BizTalk 2013 Installation and Configuration – Configure BizTalk Server SQL Jobs (Part 15), however I personally don’t like this approach for two reasons:
I don’t like to change the scripts of the standard BizTalk jobs
And I also believe that this approach is very limited and doesn’t allow the flexibility that we all want.
Instead I prefer to create an SQL Server Maintenance Plan to delete BizTalk Database Backups to accomplish this task.
Maintenance Plan to clean BizTalk Database backup’s files
Maintenance Plans allows DBA’s to have flexibility to create a workflow to execute several tasks required to make sure that database are optimized, regularly backed up, and free of inconsistencies. However almost all of these tasks are warranted by the existing BizTalk jobs and I will not use them. The only thing we need is to create a task to clean BizTalk Database backup’s files from our file system.
The main advantage of this approach is that will allow us more flexibility for further changes and we can also use them to other backup’s that we have.
How to create a Maintenance Plan to clean BizTalk Database backup’s files
As a personal note, you should ensure that SQL Server Agent service is running because the maintenance plans depend on the Microsoft SQL Server Agent in order to run on a regular basis.
Important Note: this example is created to run in a developer machine, so if you are implementing this approach in production environments make sure to keep the backups for the time you see that fit your requirements or make sure the backups are save in an external storage before you delete this files from the file system.
To create the maintenance plan you need to:
Open the SQL Server Management Studio by choosing Start > Programs > Microsoft SQL Server [edition] > SQL Server Management Studio.
Or
Press the “Windows key” to switch to Windows Server 2012 Start Screen and type “SQL Management” or “SQL” and click in “SQL Server Management Studio” option on Apps menu.
Expand the server and then the “Management” folder.
Right-click “Maintenance Plans” and select “Maintenance Plan Wizard”. This launches the wizard and you can now step through and create a plan customized to meet your maintenance requirements.
On the SQL Server Maintenance Plan Wizard page, click “Next”.
On the “Select Plan Properties” page:
In the “Name”: enter the name of the maintenance plan you are creating.
In the “Description”: enter a briefly describe your maintenance plan.
In the “Run as”: specify the credential that Microsoft SQL Server Agent uses when executing the maintenance plan – leave the default.
Select “Single schedule for the entire plan or no schedule” to specify the recurring schedule of the maintenance plan.
Under Schedule, click “Change…”
Under “Frequency”, on the “Occurs” list, select “Daily” and in the “Recurs every” box, enter how often the job schedule repeats in days: 1 Day.
Under “Daily frequency”, select “Occurs once at” and specific the time of day when the job schedule should run: 12:00:00
Leave the default values in the rest of the properties and click “OK”
Back to the Select Plan Properties page, click “Next”
On the “Select Maintenance Tasks” page, select “Maintenance Cleanup Task” from the list and click “Next”
On the “Select Maintenance Task Order” page, click “Next” to continue
On the “Define Maintenance Cleanup Task” page, specify the following properties:
Under “Delete files of the following type”: select “Backup files”
Select “Search folder and delete files based on an extension” to delete all files with the specified extension in the specified folder
Under “Folder”: specify the path and name of the folder containing the files to be deleted.
Under “File extension”: Provide the file extension of the files to be deleted.
Select also “Include first-level subfolders” option if to want to delete the files also from first-level subfolders under the folder specified in Folder.
Select “Delete files based on the age of the file at task run time” and specify the minimum age of the files that you want to delete under “Delete files older than the following” property
Specify 1 Day
Click “Next”
On the “Select Report Options“ page, click “Next”.
On the “Complete the Wizard” page, verify the choices made on the previous pages, and click Finish.
On the Maintenance Wizard Progress page, verify if every action where successful executed and then click “Close”