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

Article translations Article translations
Article ID: 907661 - View products that this article applies to.
Expand all | Collapse all

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.
Collapse this tableExpand this table
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
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 Description of the SQL Server Agent jobs in BizTalk Server 2004

Properties

Article ID: 907661 - Last Review: June 21, 2007 - Revision: 2.3
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
Keywords: 
kbbiztalk2004-2006swept kbtshoot kbprb KB907661

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com