I’ve just finished working with a client to resolve a problem with the size of their BizTalkMsgBoxDb database; the database was approximately 7Gb (the TrackingData table contained over 7.5 million rows) and growing. This growth was causing serious disk space issues – both the data file and log files were on the same partition* and as the disk ran out of space the BizTalkMsgBoxDb transaction log failed to grow, resulting in the suspension of over 1000 messages.
We tracked the culprit down to the lack of a running Host Instance with ‘Host Tracking’ enabled. After some digging and help from Microsoft UK Support (thanks Christine!) it became apparent that although the BizTalk environment was tracking data (specifically inbound and outbound message bodies in orchestrations) there were no running Host Instances with ‘Host Tracking’ enabled. As a result, tracking data was not moving from the TrackingData table into the BizTalkDTADb database.
The Tracking Data Delivery Service (TDDS)
BizTalk 2004 Message Tracking and the TDDS process work as follows:
- Data and events are tracked and stored in the BizTalkMsgBoxDb based on the settings made in HAT (tracking can be enabled for Pipelines, Orchestrations, Policies (Rules) and Messages), even if there is no host running with ‘Host Tracking’ enabled.
- Tracking Data Delivery Service (TDDS) requests are sent from hosts instances that causes data to be moved from the TrackingData table into to the BizTalkDTADb database for future tracking and report etc.
- Starting a single host instance with ‘Host Tracking’ enabled [appears] to purge the TrackingData table. I plan on doing more testing here, butany MSFT clarification on this would be helpful to aid understanding of this process.
Back at the Coalface…
The fix for the client was relatively easy – simply start a host instance with ‘Host Tracking’ enabled and watch the purge happen. Unfortunately, it wasn’t quite that simple: because the SQL Server data and log partition was running extremely short of space we needed to maintain adequate space disk space for the purge to actually take place.
To sidestep this complication, we simply truncated the BizTalkDTADb database – using Mike Holdorf’s excellent blog entry on truncating this database as a starting point** – as disk space began to run low.
Eight hours and 7.5 million rows later, the MsgBox was back to normal. Don’t you just love BizTalk!!
* – Its best practice to locate data files and log files on separate disks as isolating the transaction log can allow the disk head to be ready for the next write by not having other files contend for the physical disk resource (for more information see Kimberly L. Tripps’s 8 steps to better transaction log throughput (among others).
I’ve attached the actual SQL script used to truncate the BizTalkDTADb database to this entry. WordPress doesn’t seem to let me want to upload a .sql file (understandable), I will write on this script shortly. All credit for this goes to Mike Holdorf.