BizTalk 2006 Tracking Database Won't Shrink - Why?

Question

I am running a BizTalk 2006 server instance on a SQL 2000 SP4 Database. I have a 10 GB Tracking DDB (9GB Used / 1GB Free). I am running the DTADB Archive & Purge jobs every hour. It is purging messages at 10 Days / 14 Days Hard. It runs without error. I take the purging down to 5 Days / 9 Days Hard and the Tracking Database's size only decreases by less than 5%.

Anybody have any thoughts or experience on what my be causing this issue?

Answer 1

I think it could be due to you using SQL server 2000.

The documentation for configuring purging of the database specifically states SQL Server 2005 and 2008.

http://msdn.microsoft.com/en-us/library/aa558715(BTS.10).aspx

There are also people who have had problems running purge scripts on SQL Server 2000.

http://www.biztalkgurus.com/forums/p/9443/18513.aspx

Hope this helps

Answer 2

By default, the tracking database** won't reduce in size - I suspect that if you look at the data and log file usage, you will find a large percentage in the unallocated (data file) and unused (log file) states.

You will need to shrink the database or individual files to reduce the overall database size using the DBCC SHRINKFILE command as discussed at Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE.

Hope this helps.

** or any database for that matter, unless the AUTO SHRINK option is enabled, however this isn't recommended: SQL Server Storage Engine Blog - Turn AUTO_SHRINK off!!

Answer 3

In the end, the only solution was to manually purge the tracking DB...

http://msdn.microsoft.com/en-us/library/dd800104%28BTS.10%29.aspx

Not sure why it happens.

The DTA Archive and Purge SQL Server Agent job reduces the need to manually purge data from the BizTalk Tracking (BizTalkDTADb) database due to continuous purging of the database and compaction of stored tracking data. You might need to manually purge data if your BizTalk Tracking (BizTalkDTADb) database has grown so much that sustained performance degradation is occurring and the DTA Archive and Purge job is unable to keep up with the database growth.

Seems to imply this may be part of routine housekeeping.