Avoiding MessageBox Latency Issues
Ideally, all messages would be processed and delivered as soon as they were published to the MessageBox database and the MessageBox database would never grow to an excessive size. Any messages in the MessageBox that were no longer referenced would immediately be removed by the SQL agent jobs that periodically clean up the MessageBox database tables.
In real world scenarios, however, messages are not always received in a predictable, linear fashion. This means that the SQL agent jobs require time to clean up the MessageBox database tables.
Therefore, in some scenarios; the MessageBox can grow large very quickly.
The following items can cause the MessageBox to grow excessively large and negatively affect overall performance:
The BizTalk Host instance that has the "allow Host tracking" option set is stopped This is the host that is responsible for moving the tracking data from the MessageBox database to the BizTalk Tracking database (BizTalkDTADb).
SQL Server Agent is not running This can happen if the SQL jobs responsible for moving data from the MessageBox database to the BizTalk Tracking database [subsequently purging the moved data in the MessageBox] are not running. It is imperative for the SQL Agent Service to run all the time to avoid this problem.
SQL Server Jobs are disabled Even if the SQL Server Agent is running, it is imperative that none of the default SQL Server jobs be disabled.
BizTalk Tracking database grows excessively This can happen if the BizTalk Tracking (BizTalkDTADb) database grows very large. This causes inserts into the BizTalk Tracking database to take longer. When this happens the movement of data by Tracking Data Delivery Service (TDDS) slows down, causing a backlog build up in the MessageBox database. To avoid this problem it is important to run the SQL server archive and purge jobs periodically on the BizTalk Tracking databases.
Excessive Disk I/O Latency If the incoming rate of data into the MessageBox database is faster than the system can process and move the data to the BizTalk Tracking database, backlog can build up in the MessageBox database. If the backlog is uncontrolled, system performance will degrade over time. One way to lessen this problem is to install faster disks and/or upgrade the hardware to help ensure that the system is capable of recovering from any message backlogs experienced over time.
Even if you follow all the best practices described in this topic, over time the volume of tracking data moved to the BizTalk Tracking database will grow very large. It is important to implement a database maintenance plan to periodically archive tracking data so that the system can continue to perform optimally.
The amount of historical data that can be retained in the BizTalk Tracking database depends on the volume of messages processed by the system. For low stress and low throughput systems, the BizTalk Tracking database will grow at a slower rate and it will be possible to retain more historical data in it.
It is recommended to retain minimal data in the BizTalk Tracking database so that runtime performance is not sacrificed.