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


The information in this article applies to:

  • Microsoft BizTalk Server 2004 Standard Edition
  • Microsoft BizTalk Server 2004 Developer Edition
  • Microsoft BizTalk Server 2004 Enterprise Edition
  • Microsoft BizTalk Server 2004 Partner Edition

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.

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.

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.
  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.

MORE INFORMATION

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.
Job nameFunctionEnabled by default
MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDbReleases rows that are associated with dead processesYes
MessageBox_Message_Cleanup_BizTalkMsgBoxDbCleans up removed messages from message box tablesYes
MessageBox_Parts_Cleanup_BizTalkMsgBoxDbCleans up removed parts from message box tablesYes
PurgeSubscriptionsJob_BizTalkMsgBoxDbCleans up subscriptionsYes
TrackedMessages_Copy_BizTalkMsgBoxDbCopies tracked message bodies for tracked messagesYes
TrackingSpool_Cleanup_BizTalkMsgBoxDbSee the description in this sectionNo
Modification Type:MinorLast Reviewed:6/26/2006
Keywords:kbbiztalk2004-2006swept kbtshoot kbprb KB907661 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.