Share via

BizTalk Performance Troubleshooting: Tracking data in MsgBox db can not be moved to DTA

  • Article

We faced a strange issue on one of our servers recently. There was this orchestration it was taking close to 50 minutes to complete while the same orchestration was completing within 2 seconds on another group of server.

Note: The mgBox DB is present on different instances of SQL server for both of these BizTalk machines.

When I studied the orchestration flow it appeared that each time it tried to publish message into msgBox there was a huge delay. 

 

Thought of sharing the steps performed to get this resolved.We ran the msgBox viewer tool  diagnostic and it resulted in following errors being reported:

 

0 (Tracking data in MsgBox db can not be moved to DTA) !!

Reason: 6-DB Size !!

https://psrathoud.files.wordpress.com/2015/08/report1.png?w=945

For the error related to Tracking data in MsgBox db can not be moved to DTA  was happening because the hosts did not have the host tracking enabled which was resulting into the tracking data not being moved from msgBox DB to the trackingDB.

What we did : Enabled host tracking on each host and later we created a dedicated tracking host.  You may want to read this Microsoft article to understand why a dedicated tracking host can improve BizTalk performance.

https://psrathoud.files.wordpress.com/2014/05/hosttracking.png?w=945

Refer this for more details: http://msdn.microsoft.com/en-us/library/cc296633(v=bts.10).aspx

“A BizTalk Host that hosts tracking is responsible for moving the DTA and BAM tracking data from the MessageBox database to the BizTalk Tracking (DTA) and BAM Primary Import databases. This movement of tracking data has an impact on the performance of other BizTalk artifacts running in the same host that is hosting tracking. Thus, you should use a dedicated host that does nothing but host tracking.

Using a dedicated tracking host also allows you to stop other BizTalk hosts without interfering with BizTalk Server tracking. The movement of tracking data out of the MessageBox database is critical for a healthy BizTalk Server system. If the BizTalk Host responsible for moving tracking data in the BizTalk group is stopped, the Tracking Data Decode service will not run. The impact of this is as follows:

  • HAT tracking data will not be moved from the MessageBox database to the BizTalk Tracking database.
  • BAM tracking data will not be moved from the MessageBox database to the BAM Primary Import database.
  • Because data is not moved, it cannot be deleted from the MessageBox database.
  • When the Tracking Data Decode service is stopped, tracking interceptors will still fire and write tracking data to the MessageBox database. If the data is not moved, this will cause the MessageBox database to become bloated, which will impact performance over time. Even if custom properties are not tracked or BAM profiles are not set up, by default some data is tracked (such as pipeline receive / send events and orchestration events). If you do not want to run the Tracking Data Decode service, turn off all tracking so that no interceptors save data to the database. To disable global tracking, see “How to Turn Off Global Tracking” in BizTalk Server 2006 R2 Help at http://go.microsoft.com/fwlink/?LinkId=101589. Use the BizTalk Server Administration console to selectively disable tracking events.”

The Message publishing throttling state was in red for every host instance on the server We went ahead and check for the spool table count and realized there were close to 200000+ messages piled up there. Generally below 500 is considered a normal count for an average traffic. This count was way too high.

https://psrathoud.files.wordpress.com/2015/08/job-status.png?w=630

We went to the BizTalk jobs and identified the DTAPurge and Archive job was disabled and TrackedMessageCopy job had been failing since long. Once this was corrected and jobs were enabled the number of messages in spool table came back to normal.

https://psrathoud.files.wordpress.com/2015/08/spool.png?w=630