Microsoft KB Archive/909072

= FIX: You experience a decrease in performance when the Tracking database becomes very large in BizTalk Server 2004 =

Article ID: 909072

Article Last Modified on 6/5/2006

-

APPLIES TO


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

-





SYMPTOMS
In Microsoft BizTalk Server 2004, the Tracking database continues to grow as BizTalk Server processes data. You may notice a backlog of tracking data in the TrackingData table in the BizTalkMsgBoxDb database. When this issue occurs, you experience a decrease in performance in BizTalk Server 2004.

When the Tracking database becomes very large, the computer may run low on disk space. Additionally, the Tracking Data Delivery Service (TDDS) may time out or generate errors when the service reads from the source database. You may also notice a backlog of messages in the BizTalkMsgBoxDb database.

When this issue occurs, you may also experience the following symptoms:
 * The BizTalkMsgBoxDb database becomes very large.
 * Queries are slow or unresponsive in the Health and Activity Tracking (HAT) tool.
 * Hard disk performance decreases or the hard disk fails.
 * SQL Server performance becomes slower and response times increase.
 * Locking issues may occur when two receive locations process messages at the same time.



CAUSE
This issue may occur if data is not purged from the BizTalkMsgBoxDb database or the Tracking database.



Hotfix information
A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next BizTalk Server 2004 service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the hotfix. For a complete list of Microsoft Product Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

Prerequisites
You must have Microsoft BizTalk Server 2004 Service Pack 1 (SP1) installed to apply this hotfix.

Note After you extract the files that are included in this hotfix, run the Setup program in the Pre-qfe folder. Do this before you run the Setup program in the Qfe folder.

Restart requirement
You must restart the computer after you apply this hotfix.

Hotfix replacement information
This hotfix does not replace any other hotfixes.

File information
The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

Note Because of file dependencies, the most recent hotfix that contains these files may contain additional files.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



MORE INFORMATION
The BizTalkMsgBoxDb database and the Tracking database can grow very large for many reasons. BizTalk Server supports many different solution architectures, configurations, and topologies. Because of this flexibility, the size of the BizTalkMsgBoxDb database and tracking database may vary. The following variables contribute to database growth:
 * The number of pipelines
 * The number of orchestrations
 * The number of message properties that are tracked
 * The number of messages that are tracked

BizTalk Server stores lots of information about BizTalk Server services in the Tracking database. As the size of the Tracking database grows, you may experience decreased performance and query timeouts. If you leave the Tracking database unattended, the database may fail.

The BizTalk Server administrator is responsible for performing the maintenance tasks that BizTalk Server requires to maintain the health of the Tracking database. The administrator may not regularly perform maintenance tasks on the Tracking database because management of this database does not affect the business process design. Management of this database also does not affect aspects of the BizTalk Server runtime.

To determine whether the Tracking database is in an unhealthy state, compare the current size of the Tracking database to an estimation of the correct size of a healthy Tracking database. For information about how to estimate the size of a healthy Tracking database, see the link to the DTA sizing white paper in the &quot;References&quot; section.

BizTalk Server 2004 uses a set of SQL Server Agent jobs to perform message garbage collection. If the SQL Server Agent jobs are not running, the BizTalkMsgBoxDb database can grow unexpectedly. Additionally, the Tracking database may grow very large if you do not regularly remove the Tracking database archives. How you manage the Tracking database archives is a business decision.

Sample scripts to help move data from the BizTalkMsgBoxDb database and to help clear the Tracking database are located in the Microsoft BizTalk Server 2004\SDK folder. These sample scripts are installed when you install BizTalk Server 2004. By default, these scripts are not automated.

If you do not apply the hotfix that is described in this article, there is no automated method the clear the Tracking database of unused data. Additionally, the BizTalk Server documentation does not provide information about how to archive and how to access tracking data. Therefore, you might overlook maintenance of the Tracking database.

The hotfix that is described in this article provides several features to maintain system health and to archive tracking data for future use. This hotfix provides automated methods to directly copy tracked messages into the Tracking database. This hotfix also provides scheduled SQL Server Agent jobs to archive data and to clear data in the Tracking database. Additionally, this hotfix resolves a known database lock contention issue that may occur when large flat file messages are disassembled in a pipeline.

The following features are also included with this hotfix:
 * You can automatically clear and archive data in the Tracking database based on specific parameters.
 * You can configure a time interval to automatically delete data that is older than a specified date.
 * You can use the CopyTrackedMessageToDTA option to copy tracked messages from the BizTalkMsgBoxDb database to the Tracking database by using a linked server.
 * You can use a secondary database server to validate archives as the archives are created. This feature is optional.
 * You can use the Health and Activity Tracking (HAT) tool together with old tracking database archives. You can use HAT together with old tracking database archives when the current schema and the old schema differ.
 * A feature in this hotfix can reduce the amount of generated tracking data without reducing the information that is stored. This behavior can slow the growth of the BizTalk Server databases.
 * This hotfix optimizes database schemas.
 * This hotfix increases performance in HAT queries when you search for messages and service instances on large databases.

Note For information about how to apply and configure this hotfix, see the Archiving and Purging QFE for BizTalk 2004.doc file that is included with this hotfix.

