The Tracking_Spool1 or Tracking_Spool2 tables in the BiztalkMsgBoxDb database become very large in BizTalk Server 2004

In Microsoft BizTalk Server 2004, you notice that the Tracking_Spool1 or Tracking_Spool2 tables in the BiztalkMsgBoxDb database become very large and consume lots of disk space.

This issue occur when one of the following conditions is true:

• You configure Health and Activity Tracking (HAT) to track message bodies for pipelines and orchestrations.
• You configure the Tracking Type property for a send port or a receive port to Before Receive or After Receive.

To work around this issue, enable the TrackingSpool_Cleanup_BizTalkMsgBoxDb job in SQL Server Agent. To enable the TrackingSpool_Cleanup_BizTalkMsgBoxDb job, follow these steps:

Notes

• By default, the TrackingSpool_Cleanup_BizTalkMsgBoxDb job is not enabled.
• The TrackingSpool_Cleanup_BizTalkMsgBoxDb job deletes message body data. If you want to save all message body data, follow the steps in the "More Information" section.

1. Start Enterprise Manager.
2. Expand Microsoft SQL Server, expand SQL Server Group, expand the computer that you want, expand Management, and then expand SQL Server Agent.
3. In the left pane, click Jobs.
4. In the right pane, right-click TrackingSpool_Cleanup_BizTalkMsgBoxDb, and then click Enable Job.

The BizTalkMsgBoxDb database contains the Tracking_Spool1 table and the Tracking_Spool2 table. If you have not run the TrackingSpool_Cleanup_BizTalkMsgBoxDb job before, only one of these tables contains any rows.

The first time that you run the TrackingSpool_Cleanup_BizTalkMsgBoxDb job, the Tracking_Spool2 table is empty and is the inactive table. The TrackingSpool_Cleanup_BizTalkMsgBoxDb job purges the Tracking_Spool2 table and then marks the Tracking_Spool1 table as inactive. The job also marks the Tracking_Spool2 table as the active table. The BizTalkMsgBoxDb database then logs message body data in the Tracking_Spool2 table.

The next time that you run the TrackingSpool_Cleanup_BizTalkMsgBoxDb job, the job purges the inactive Tracking_Spool1 table, marks the Tracking_Spool1 table as active, and then marks the Tracking_Spool2 table as inactive. Therefore, if you run the TrackingSpool_Cleanup_BizTalkMsgBoxDb job one time every two weeks, you will have two weeks of data for message body data tracking.

If you do not want to delete any message body data, use the Archivemessages.vbs file to archive message body data. Then use the Restoremessages.vbs file to restore data when the data is required. By default, the Archivemessages.vbs and Restoremessages.vbs files are located in the following folder: The following table lists SQL Server Agent jobs that perform database maintenance tasks in BizTalk Server 2004. For more information about the SQL Server Agent jobs in BizTalk Server 2004, click the following article number to view the article in the Microsoft Knowledge Base: