BizTalk Developers, have you enabled DTA Purge and Archive (BizTalkDTADb) SQL job on your development machine?

In development machines, BizTalk developers normally tend to enable full body tracking of messages they process inside the BizTalk server for debugging purpose. As BizTalk Server processes more and more data on your system (over a period of time), the BizTalk Tracking (BizTalkDTADb) database continues to grow in size. Unchecked growth decreases system performance and may generate errors in the Tracking Data Delivery Service (TDDS). In addition to general tracking data, tracked messages can also accumulate in the MessageBox database, causing poor disk performance. According to this MSDN article http://msdn2.microsoft.com/en-us/library/aa560754.aspx By default, the DTA Archive and Purge job is not enabled. You must first configure and then enable the job I haven’t enabled the job after BizTalk server installation on my development machine and recently when I visited the SQL Data folder for something else I saw this shocking file size (shown below after 6 months of usage). My BizTalk tracking data file is 3.05GB.

Purge tracking data:

BizTalk 2006 got some nice Stored Procedures and SQL Jobs to Archive and Purge the tracking database. BizTalk 2006 gives you an option to Archive and Purge the tracking data or just purge the data without archiving. In a development machine we don’t need to maintain the archived tracking data, so we will just purge it periodically. As soon as I saw the data file size of 3.05GB, first thing I did was configured the DTA Purge and Archive SQL job to just purge the tracking data as explained in the article http://msdn2.microsoft.com/en-us/library/aa578470.aspx Long story short: 1. Change the SQL Statement inside “DTA Purge and Achieve” SQL Job to declare @dtLastBackup datetime set @dtLastBackup = GetUTCDate() exec dtasp_PurgeTrackingDatabase 1, 0, 1, @dtLastBackup If your data file size is too big (like mine 3.05GB) after configuring DTA Purge and Achieve SQL job don’t go and run the SQL Job directly, due to the volume of data present in the tracking database, it will take very long time to process the backlog. Instead do a manual purge first as explained in this article http://msdn2.microsoft.com/en-us/library/aa561918.aspx Long story short: 1. Stop all BizTalk/IIS service (SSO, Rule,EDI,Host Instances) 2. Open SQL Management Studio and run the following stored procedure under BizTalkDTADb database as shown below: EXEC  [BizTalkDTADb].[dbo].[dtasp_PurgeAllCompletedTrackingData] The stored procedure will be executed very quickly, mine took less than 10 seconds (remember my data file size was 3.05GB).

Shrink your BizTalkDBADb via SQL Management Studio:

Right-Click on the BizTalkDTADb database inside management studio and select Tasks => Shrink => Database, and click “OK” Now, look at the size of the BizTalkDTADb.mdf file, its shrinked to the absolute minimum value.

Configure the SQL Job to run periodically (every 1 minute)

After performing the steps above (once the tracking data is purged)  “DTA Purge and Archive (BizTalkDTADb)” SQL Job will run instantaneously without any wait. Now, you can enable the job to run periodically. Nandri! Saravana