Nick Heppleston’s BizTalk Blog

In BizTalk 2004 SP2, the BizTalk Team gave us the Archive and Purge SQL Server Maintenance Job for managing the size of the Tracking Database. This was a great tool and really took away some of the admin headaches in maintaining this particular database.

The job allows administrators to archive olde tracking data and verify the integrity of the backup before purging from the live database (for detailed instructions, see the MSDN website). This is a good, pro-active practice for the health of any BizTalk environment, in that:

• BizTalkDTADb database growth is constantly checked, allowing the TDDS service to run effectively, thereby maintaining a healthy Message Box.
• Maintaining the size of the BizTalkDTADb data and log files ensures that the database doesn’t just eat all of your available disk space.
• The tracking data can be restored in a dedicated OLAP environment, allowing reports to be run without affecting the live BizTalk environment.

However, imagine a scenario where you don’t want the archived data – you simply want to purge. Where that’s the case, you can either manually run a truncate on the BizTalkDTADb (see my posts detailing how to do this under BizTalk 2004 or BizTalk 2006 – there are some subtle differences), or run the hidden admin gem dtasp_PurgeTrackingDatabase (a stored procedure used by the dtasp_ArchiveAndPurgeTrackingDatabase which just purges) on a scheduled basis, so you no longer need to worry about manually purging.

Configuring the dtasp_PurgeTrackingDatabase Stored Procedure

The Purge stored procedure is used in a very similar manner to dtasp_ArchiveAndPurgeTrackingDatabase, taking 4 parameters:

• Live Hours – Any completed instance older than the live hours + live days…
• Live Days – …will be deleted along with all associated data.
• Hard Days – all data older than this will be deleted. The time interval specified here should be greater than the live window of data.
• Last Backup – UTC Datetime of the last backup. When set to NULL, data is not purged from the database.

As an example of its usage, if you wanted to purge any tracking data older than two hours and hard-delete any data older than one day, you would use the follow T-SQL:


–– Change to the BizTalk Tracking database.
USE BizTalkDTADb
GO

–– Prune tracking data older than two hours.
DECLARE @dtLastBackup DATETIME
SET @dtLastBackup = GetUTCDate()
EXEC dtasp_PurgeTrackingDatabase 2, 0, 1, @dtLastBackup

The @dtLastBackup parameter is used to ensure that records that have not been backed-up are not deleted by the purge procedure, so we set it to the current UTC date/time to ensure that whatever live hours/days you specify, records are deleted. I’m not too sure why the development team included this as a parameter: the procedure is a wrapper that calls the dtasp_PurgeTrackingDatabase_Internal (which is also called by the dtasp_ArchiveAndPurgeTrackingDatabase procedure) so it could have been included in that wrapper given that it is always defaulted to the current UTC date/time during purges!

One other thing to note is that the wrapper script also modifies the prune before date (the date that is built by the live hours/days specified): this date date is tweaked to remove ten minutes to ensure redundancy in the remaining data.  In the example used above, rather than keeping two hours of data, there will in fact be two hours and ten minutes once the code has been run.

Using the dtasp_PurgeTrackingDatabase Stored Procedure

To start using this procedure, I would suggest that you first truncate your database, then configure the SQL Server Agent job to run every X hours/days depending on your environmental requirements. I suggest initially truncating your database to ensure the first run of the job doesn’t take several hours!

Further details about the dtasp_PurgeTrackingDatabase stored procedure can be found online at MSDN.