Microsoft KB Archive/907661

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

Article ID: 907661

Article Last Modified on 6/21/2007

-

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 &quot;More Information&quot; 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.

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

Keywords: kbbiztalk2004-2006swept kbtshoot kbprb KB907661

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.