Configuring the BizTalk SQL Agent Jobs

One of the things people often forget to do when installing BizTalk into an environment for the first time is to configure and enable two critical SQL Server Agent jobs:

• Backup BizTalk Server
• DTA Purge and Archive

These jobs are not enabled by default because they both require you to supply UNC paths to the backup and archive file locations, respectively.  While backups and archiving may not seem like the most immediate priority (especially in non-production environments), it is nonetheless essential that these jobs run successfully as they keep the BizTalk databases “lean & mean” by purging old data. I’ve seen client tracking databases grow to over 200GB in size when the latter job is not running properly (the Microsoft recommended size is <5GB!!)

Of course, when the jobs are configured correctly (following the MSDN instructions here and here), this introduces the problem of disk space taken up by the backup and the archive files. Out of the box, BizTalk offers no solution for managing these files, which will continue to accumulate over time. And yes, I’ve seen more than one client run out of disk space on their SQL Server for this very reason!

My very wise friend and co-worker Bill Chesnut (aka “BizTalk Bill”) has created a modified stored procedure that cleans up the backup files, which is a big help. I highly recommend implementing this in all your environments (although if does require xp_cmdshell to be enabled on your SQL Server). I’ve incorporated this script into my configuration below.

Below I have pasted the template for both of these jobs, partly because the formatting in SSMS always seems to obscure the parameters, but mostly because I’m always looking for this template and have to search for it (now I know where to find it!).

Backup BizTalk Server

Note that I’ve used the optional parameters to control the full backup time (by default, this is midnight GMT, which is 10:00am here in Queensland, Australia). I’ve also substituted the OOTB ‘sp_DeleteBackupHistory’ job replacing it with Bill’s modified stored procedure as discussed above.

STEP 1: Set Compression Option:

exec [dbo].[sp_SetBackupCompression] @bCompression = 1 /* 0 – do not use Compression, 1 – Use Compression */

STEP 2: BackupFull:

exec [dbo].[sp_BackupAllFull_Schedule]
‘d’ /* Frequency */,
‘BTS’ /* Name */,
‘<Backup File Location>‘, /* location of backup files */
0,    /* 1 = force full backup on incomplete set */
23    /* backup at 11:00 pm */

STEP 3: MarkAndBackupLog:

exec [dbo].[sp_MarkAll]
‘BTS’ /*  Log mark name */,
‘<Backup File Location>‘ /* location of backup files */,
1  /* puts logmark filename in local time */

STEP 4: Clear Backup History:

exec [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep=14, @UseLocalTime=1

DTA Purge and Archive

I’ve included two samples below. The first one is the default OOTB version that actually archives the purged records off to the file system. This is the one you are likely to use in production:

STEP 1: Archive and Purge:

exec dtasp_BackupAndPurgeTrackingDatabase
0, --@nLiveHours tinyint,           --Any completed instance older than the live hours +live days
1, --@nLiveDays tinyint = 0,        --will be deleted along with all associated data
30, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
‘<Archive File Location>‘, --@nvcFolder nvarchar(1024) = null,
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0           --not currently used

This second version calls a different stored procedure that purges the tracking database but does not archive any files (as documented on MSDN here). I always use this on my development machine, and often in lower test environments where there is usually no need to review tracking data over a week old. This is especially handy in environments where you have message body tracking turned on (for debugging support), because this greatly increases the size of the tracking data files:

STEP 1: Archive and Purge:

exec dtasp_PurgeTrackingDatabase
0, --@nHours tinyint,        --Any completed instance older than the live hours +live days
7, --@nDays tinyint = 0,     --will be deleted along with all associated data
8, --@nHardDays tinyint = 0, --all data older than this will be deleted.
null --@dtLastBackup         --just ensures we don’t archive data newer than last backup; leave null if you don’t care

Hope this helps you keep your BizTalk databases trim, taught and terrific!