BizTalkDTADb grows too large – How to purge and maintain the database?

The BizTalkDTADb is a BizTalk database that stores health monitoring data tracked by the BizTalk Server tracking engine. It is commonly called the “BizTalk Tracking Database”. This database can grow relatively quickly in size depending of the kind of load your server is under.

I will explain first what should be done to keep the database healthy (by which I mean to keep it under a reasonable size) and after how to clean up the database if it grew up so large that the normal clean up method doesn’t work anymore.

1. How to maintain the BizTalkDTADb?

Each BizTalk service instance running is processing data and while the data is processed, BizTalk tracks it and saves it in the BiztTalk Tracking Database. This means that the Tracking Database will grow indefinitely over time which is obviously not a viable option.
There is an SQL job called “DTA Purge and Archive (BizTalkDTADb)” that is installed on the BizTalk SQL Server which is used for cleaning up (deleting old tracking information) the BizTalkDTADb. That job is not enabled by default so the first thing that should be done after installing BizTalk server is to configure and enable the job. See here for information about how the cleanup process works and here for information on how to configure the SQL job. Basically, the job calls a single stored procedure on the BizTalkDTADb and once edited should looks like the following:

exec [dbo].[dtasp_BackupAndPurgeTrackingDatabase] 1, 0, 1, ‘\\MyBizTalkServer\backup’, null, 0

The 4 first parameters are the one that you need to know about. The 2 first are the number of hours and days for which completed instances will be cleaned up. The third one is the number of days after which even non completed instance will be cleaned up. The fourth is the location of the backup folder.
This means that the SQL job will back up the BizTalkDTADb each time it runs, making that backup files will fill up your disk subsystem pretty quickly if nothing is done about it! Backups are important in case of a Database crash and that the Tracking Database needs to be restored.

If you do not consider the Tracking Database to be of enough importance to be backed up and have the extra burden to manage the backups, you can modify the “DTA Purge and Archive (BizTalkDTADb)” SQL job as explained here. This way, the job will only purge the tracking database without backing it up. It is especially applicable for development and QA environments and might also apply to your production environment.
In short, the only change that needs to be done in the SQL job is to modify the T-SQL statement run by the job. It needs to execute the SP dtasp_PurgeTrackingDatabase instead of dtasp_BackupAndPurgeTrackingDatabase.

The final T-SQL statement executed by the SQL job will be similar to the following:

declare @now as datetime
set @now = GetUTCDate()
exec [dbo].[dtasp_PurgeTrackingDatabase] 0, 3, 6, @now

In this case I keep complete instances in the Tracking DB for 3 days and incomplete one for 6 days, everything older should be purged. As you see there is no path to specify for the backup location as no database backup is executed.

Instead of modifying the original SQL job you could alternatively disable it and create a new job with the appropriate T-SQL call. That is how I have done it myself and consider it to be a best practice.
Moreover, I scheduled the job to run every 5 minutes. This has proven to be a good time interval. I used to run the job every 30 minutes only but I ever encountered cases where the clean up procedure did not keep up with the amount of tracked data and I ended up with a huge tracking database which I had to purge manually, as I will explain next.
So, a 5 minutes interval to run the job seems to also be a best practice from my experience.

2. How to manually purge the BizTalkDTADb?

You will have to manually purge the BizTalkDTADb database if it grew too large either because the clean up procedure was not started or the clean up procedure could not keep up with the amount of data saved in the Tracking Database.
This is explained in details here but, in short, the important points are:

– All the BizTalk services used by BizTalk needs to be stopped. This means all the BizTalk host service instances, Enterprise SSO, BizTalk Rules Engine, EDI service, BAM, BAS and IIS if they are used.

– Open Microsoft SQL Server Management Studio and run the following SQL statement on the BizTalkDTADb: exec dtasp_PurgeAllCompletedTrackingData

Once the procedure is executed, a lot of space will have been freed in the Tracking Database. The database will nevertheless still take the same amount of space on the disk subsystem because deleting data in a database does not reduce the size the database takes on the disk. If you want to reduce its size on the disk, you need to shrink it. You can do that in 2 ways:

1. Through SQL Server Management Studio, right click on the BizTalkDTADb database, click on Tasks > Shrink > Database

2. Through T-SQL using the DBCC SHRINKDATABASE command:
DBCC SHRINKDATABASE (BizTalkDTADb);
The reference of the T-SQL DBCC SHRINKDATABASE command can be found here.

Another useful trick is the ability to truncate the Log file (which should not be done on production as it “breaks” the backup). See some information about it here: Truncating the Database Log File.