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

|  Posted: February 20, 2007  |  Categories: BizTalk Server

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.

biztalk tracking data file

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”

biztalkdtadb

Now, look at the size of the BizTalkDTADb.mdf file, its shrinked to the absolute minimum value.

biztalk tracking data file

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

Author: Saravana Kumar

Saravana Kumar is the Founder and CTO of BizTalk360, an enterprise software that acts as an all-in-one solution for better administration, operation, support and monitoring of Microsoft BizTalk Server environments.

One Platform Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

One Platform - Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

One Platform - Operations, Monitoring and Analytics Software
ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

Back to Top