Select the product you need help with

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

Article ID: 907661 - View products that this article applies to.

SYMPTOMS

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.

Back to the top | Give Feedback

CAUSE

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.

Back to the top | Give Feedback

WORKAROUND

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.

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

Back to the top | Give Feedback

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:

\Program Files\Microsoft BizTalk Server 2004\SDK\Samples\Admin\Database Maintenance

The following table lists SQL Server Agent jobs that perform database maintenance tasks in BizTalk Server 2004.

Collapse this tableExpand this table

Job name	Function	Enabled by default
MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb	Releases rows that are associated with dead processes	Yes
MessageBox_Message_Cleanup_BizTalkMsgBoxDb	Cleans up removed messages from message box tables	Yes
MessageBox_Parts_Cleanup_BizTalkMsgBoxDb	Cleans up removed parts from message box tables	Yes
PurgeSubscriptionsJob_BizTalkMsgBoxDb	Cleans up subscriptions	Yes
TrackedMessages_Copy_BizTalkMsgBoxDb	Copies tracked message bodies for tracked messages	Yes
TrackingSpool_Cleanup_BizTalkMsgBoxDb	See the description in this section	No

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:

919776

(http://support.microsoft.com/kb/919776/ )

Description of the SQL Server Agent jobs in BizTalk Server 2004

Back to the top | Give Feedback