maintain and troubleshoot BizTalk Server databases

How to maintain and troubleshoot BizTalk Server databases

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

Expand all | Collapse all

On This Page

SUMMARY

Microsoft BizTalk Server databases and the health of the databases are very important for a successful BizTalk Server messaging environment. This article discusses important things to consider when you work with BizTalk Server databases. These considerations include the following:

• You must disable the Auto Update Statistics and Auto Create Statistics Microsoft SQL Server options.
• You must set the Max Degree of Parallelism property correctly.
• Determine when you can rebuild BizTalk Server indexes.
• Locking, deadlocking, or blocking may occur.
• You may experience issues with large databases or tables.
• BizTalk SQL Server Agent jobs
• Service instances may be suspended.
• You may experience SQL Server and BizTalk Server performance issues.
• You should follow best practices in BizTalk Server.

Back to the top | Give Feedback

INTRODUCTION

This article describes how to maintain BizTalk Server databases and how to troubleshoot BizTalk Server database issues.

Back to the top | Give Feedback

MORE INFORMATION

Known issues

You must disable the Auto Update Statistics and Auto Create Statistics options

You must disable the Auto Create Statistics and Auto Update Statistics options on the BizTalkMsgBoxDb database. To determine whether these settings are disabled, execute the following stored procedures in SQL Server:

exec sp_dboption 'BizTalkMsgBoxDB', 'auto create statistics'
exec sp_dboption 'BizTalkMsgBoxDB', 'auto update statistics'

You should set the CurrentSetting setting to off. If this setting is set to on, turn it off by executing the following stored procedures in SQL Server:

exec sp_dboption 'BizTalkMsgBoxDB', 'auto create statistics', 'off'
exec sp_dboption 'BizTalkMsgBoxDB', 'auto update statistics', 'off'

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

917845 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server

912262 The auto update statistics option, the auto create statistics option, and the Parallelism setting are turned off in the SQL Server database instance that hosts the BizTalk Server BizTalkMsgBoxDB database

You must set the Max Degree of Parallelism property correctly

On the computer that is running SQL Server and hosting the BizTalkMsgBoxDb database, set the Max Degree of Parallelism run_value and config_value properties to a value of 1. To determine the Max Degree of Parallelism setting, execute the following stored procedure against the Master database in SQL Server:

exec sp_configure 'max degree of parallelism'

If the run_value and config_value properties are not set to a value of 1, execute the following stored procedure in SQL Server to set them to 1:

exec sp_configure 'max degree of parallelism', '1'
reconfigure with override

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

899000 The Parallelism setting for the instance of SQL Server when you configure BizTalk Server

917845 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server

Determine when you can rebuild BizTalk Server indexes

Most BizTalk Server indexes are clustered (index ID: 1). You can use the DBCC SHOWCONTIG SQL Server statement to display fragmentation information for the BizTalk Server tables.

The BizTalk Server indexes are GUID-based. Therefore, fragmentation typically occurs. If the Scan Density value that is returned by the DBCC SHOWCONTIG statement is less than 30 percent, the BizTalk Server indexes can be rebuilt during downtime.

Many BizTalk Server tables contain columns that use DataType definitions. Online indexing cannot be performed in these columns. Therefore, you should never rebuild the BizTalk Server indexes while BizTalk Server processes data.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

917845 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server

For more information about how to analyze the DBCC SHOWCONTIG statement output, visit the following Microsoft website:

http://technet.microsoft.com/en-us/library/cc966523.aspx

Locking, deadlocking, or blocking may occur

Typically, locks and blocks occur in a BizTalk Server environment. However, these locks or blocks do not remain for an extended time. Therefore, blocking and deadlocking indicate a potential problem.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

917845 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server

You may experience issues with large databases or tables

We have seen that when the BizTalkMsgBoxDb database is larger than 5GB, performance problems can occur. Ideally, the BizTalkMsgBoxDb database should not be holding any data. The BizTalkMsgBoxDb database should be considered a buffer until the data is processed or moved to the BizTalkDTADb database.

An environment that uses a powerful SQL Server at the back end and many long-running orchestrations may have a BizTalkMsgBoxDb database that is larger than 5 GB. A high-volume environment that uses no long-running orchestrations should have a BizTalkMsgBoxDb database that is much smaller than 5 GB.

The BizTalkDTADb database does not have a set size. However, if performance decreases, the database is probably too large. Typically, 15 GB to 20 GB is considered too large. When you have large BizTalk Server databases, you may experience the following issues:

• The BizTalkMsgBoxDb database continues to grow. However, both the log file and the data size remain large.
• BizTalk Server takes a longer time than usual to process even a simple message flow scenario.
• Health and Activity Tracking (HAT) queries take a longer time than usual and may time out.
• The database log file is never truncated.
• The BizTalk SQL Server Agent jobs run slower than usual.
• Some tables are significantly larger or have too many rows compared to the usual table size.

Databases can become large for various reasons. These reasons may include the following:

• BizTalk SQL Server Agent jobs are not running
• Large number of suspended instances
• Disk failures
• Tracking
• Throttling
• SQL Server performance
• Network latency

Make sure that you know what is expected in your environment to determine whether a data issue is occurring.

By default, tracking is enabled on the default host. BizTalk requires that the Allow Host Tracking option be checked on a single host. When tracking is enabled, the Tracking Data Decode Service (TDDS) moves the tracking event data from the BizTalkMsgBoxDb database to the BizTalkDTADb database. If the tracking host is stopped, TDDS does not move the data to the BizTalkDTADb database and the TrackingData_x_x tables in the BizTalkMsgBoxDb database will grow.

We recommend that you dedicate one host to tracking. To allow for TDDS to maintain new tracking events in high-volume scenarios, create multiple instances of a single tracking host. No more than one tracking host should exist.

There can be too many rows in a table. There is no set number of rows that are too many. Additionally, this number of rows varies by what kind of data is stored in the table. For example, a dta_DebugTrace table that has more than 1 million rows probably has too many rows. A HostNameQ_Suspended table that has more than 200,000 rows probably has too many rows.

Use the correct BizTalk SQL Server Agent jobs

The BizTalk SQL Server Agent jobs are important for managing the BizTalk Server databases and for maintaining high performance.

The Backup BizTalk Server SQL Server Agent job is the only supported method to back up the BizTalk Server databases. This job requires all BizTalk Server databases use a Full Recovery Model. You should configure this job for a healthy BizTalk Server environment. The SQL Server methods can be used to back up the BizTalk Server databases only if SQL Server Agent is stopped and if all BizTalk Server host instances are stopped.

The MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job runs infinitely. Therefore, the SQL Server Agent job history never displays a successful completion. If a failure occurs, the job restarts within one minute and continues to run infinitely. Therefore, you can safely ignore the failure. Additionally, the job history can be cleared. You should only be concerned if the job history reports that this job constantly fails and restarts.

The MessageBox_Message_Cleanup_BizTalkMsgBoxDb SQL Server Agent job is the only BizTalk Server job that should not be enabled because it is started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job.

The DTA Purge and Archive SQL Server Agent job helps maintain the BizTalkDTADb database by purging and archiving tracked messages. This job reads every row in the table and compares the time stamp to determine whether the record should be removed.

All BizTalk SQL Server Agent jobs except the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job should be running successfully.

For more information about all the BizTalk Server SQL Server Agent jobs, 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

Service instances may be suspended

Service instances can be suspended (resumable) or suspended (not resumable). These service instances may be Messaging, Orchestration, or Port.

These service instances can make the BizTalkMsgBoxDb database grow unnecessarily and can be terminated. The following table lists what method can be used, depending on the BizTalk version:

	Group Hub	HAT	Terminate.vbs	Terminator Tool
BizTalk Server 2010	Yes	No	Yes	Yes
BizTalk Server 2009	Yes	No	Yes	Yes
BizTalk Server 2006 R2	Yes	Yes	Yes	Yes
BizTalk Server 2006	Yes	Yes	Yes	Yes
BizTalk Server 2004	No	Yes	Yes	Yes

For more information about the Terminate.vbs script, visit the following MSDN website:

http://msdn.microsoft.com/en-us/library/bb203857.aspx

Caching instances do not appear in the Group Hub page, and you cannot suspend or terminate them. This restriction is a common cause of table growth. To prevent new zombie messages for the cache service instances in BizTalk Server 2006, install the hotfix in Microsoft Knowledge Base article 936536. This issue is fixed in BizTalk Server 2006 R2 and later versions.

Note A zombie message is a message that was routed but not consumed.

For more information, click the following article number to view the article in the Mirosoft Knowledge Base:

936536 FIX: You experience performance issues with BizTalk Server 2006 and throttling messages are logged in the performance log file

When a BizTalk Server host instance terminates, caching instances may not be removed. To resolve this behavior in BizTalk Server 2006, install the hotfix in Microsoft Knowledge Base article 944426. In BizTalk Server 2006 R2, install BizTalk 2006 R2 Service Pack 1. This issue is fixed in BizTalk Server 2009 and later versions.

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

974563 List of Microsoft BizTalk Server hotfixes that are included in BizTalk Server 2006 R2 Service Pack 1

944426 FIX: Orphaned cache instances may be built in the Instances and Hosts Queue tables of the BizTalkMsgBoxDb database in BizTalk Server 2006 and in BizTalk Server 2006 R2

Back to the top | Give Feedback

Another common issue is that Routing Failure Reports (RFRs) may build up in the BizTalkHostQ and BizTalkHostQ_Suspended tables. The RFRs are not removed, and this behavior may cause the BizTalkMsgBoxDb database to grow. To address this issue in BizTalk Server 2006, install the hotfix in Microsoft Knowledge Base article 941690. This issue is fixed in BizTalk Server 2006 R2 and later versions.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

941690 FIX: Routing failure reports are not removed from the <BizTalkHostName>Q_Suspended table on a BizTalk Server 2006 server

Back to the top | Give Feedback

The terms "orphan messages" and "zombie messages" are frequently used interchangeably.

An orphan message is a message that does not have an associated instance. For example, a routing failure report is an orphan message.

A zombie message is a message that was routed but not consumed. For example, a message was delivered to a convoy orchestration. However, the convoy orchestration went down another code path. The orchestration instance finishes. The message is discarded and is now known as a zombie message.

For a description of zombie messages, visit the following MSDN website:

http://blogs.msdn.com/biztalk_core_engine/archive/2004/06/30/169430.aspx

You may experience SQL Server and BizTalk Server performance issues

BizTalk Server makes hundreds of short, very quick transactions to SQL Server within a minute. If the SQL Server cannot sustain this activity, BizTalk Server may experience performance issues. In Performance Monitor, monitor the Avg. Disk sec/Read, Avg. Disk sec/Transfer and Avg. Disk sec/Write Performance Monitor counters in the PhysicalDisk performance object. The optimal value is less than 10 ms (milliseconds). A value of 20 ms or larger is considered poor performance.

For more information about SQL Server performance, visit the following Microsoft website:

http://technet.microsoft.com/en-us/library/cc966540.aspx

For more information about BizTalk Server 2004 database high availability, visit the following MSDN website:

http://msdn.microsoft.com/en-us/library/ms942187.aspx

For more information about BizTalk Server 2006 database high availability, visit the following MSDN website:

http://msdn.microsoft.com/en-us/library/aa559920.aspx

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

298475 How to troubleshoot SQL Server performance issues

271509 How to monitor blocking in SQL Server 2005 and in SQL Server 2000

Best Practices in BizTalk Server

Start SQL Server Agent on the SQL Server. When the SQL Server Agent is stopped, the built-in BizTalk SQL Server Agent jobs that are responsible for database maintenance cannot run. This behavior causes database growth, and this growth may cause performance issues. BizTalk Server database maintenance has greatly improved in BizTalk Server 2004 Service Pack 2 (SP2) and later versions.

Put the SQL Server LDF and MDF files on separate drives. When the LDF and MDF files for the BizTalkMsgBoxDb and BizTalkDTADb databases are on the same drive, disk contention can occur.

If you do not benefit from message body tracking, do not enable this feature. However, it is a good idea to enable message body tracking while you develop and troubleshoot a solution. If you do this, make sure that you disable message body tracking when you are finished. When message body tracking is enabled, the BizTalk Server databases grow. If there is a business need that requires enabling message body tracking, confirm that the TrackedMessages_Copy_BizTalkMsgBoxDb and DTA Purge and Archive SQL Server Agent jobs are running successfully.

Typically, smaller transaction logs cause better performance. To keep the transaction logs smaller, configure the Backup BizTalk Server SQL Server Agent job to run more frequently. For more information about BizTalk Server optimization, visit the following MSDN website:

http://msdn.microsoft.com/en-us/library/bb743398.aspx

The sp_ForceFullBackup stored procedure in the BizTalkMgmtDb database can also be used to help perform an ad-hoc full backup of the data and log files. The stored procedure updates the adm_ForceFullBackup table with a value 1. The next time theBackup BizTalk Server job runs, a full database backup set is created.

The BizTalk Server Best Practices Analyzer (BPA) can be used to evaluate an existing BizTalk Server deployment. The BPA performs numerous database-related checks. For more information about the BPA, visit the following Microsoft website:

http://www.microsoft.com/downloads/details.aspx?FamilyID=93d432fe-1370-4b6d-aaa8-a0c43c30f5ab&displaylang=en

Troubleshooting

The best troubleshooting steps for the BizTalk Server SQL Server databases depend on the kind of database issue, such as blocking or deadlocking. To troubleshoot a BizTalk Server database issue, follow these steps.

Step 1: Enable and run all required BizTalk SQL Server Agent jobs

All the BizTalk SQL Server Agent jobs except the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job should be enabled and running successfully. Do not disable any other job.

If a failure occurs, use the View History option in SQL Server to view the error information, and then troubleshoot the failure accordingly. Remember that the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job runs infinitely. Therefore, you should only be concerned if the job history reports that the job constantly fails and restarts.

Step 2: Use the MsgBoxViewer tool

Collect MsgBoxViewer data while you reproduce an issue.

The MsgBoxViewer tool is useful for troubleshooting because it provides an HTML report that has detailed information about table sizes and the row count. The report can also help determine whether BizTalk Server is throttling. Additionally, the tool provides a snapshot of the BizTalk Server databases and the BizTalk Server configuration.

For more information about how to download the MsgBoxViewer tool, visit the following Microsoft website: 

http://blogs.technet.com/jpierauc/pages/msgboxviewer.aspx

For more information about throttling in BizTalk Server, visit the following MSDN website: 

http://msdn.microsoft.com/en-us/library/aa559893.aspx

When BizTalk Server is running slower than usual, run the MsgBoxViewer tool, and then review the generated HTML report for any problems. The Summary section lists warnings in yellow and potential problems in red.

Additionally, you can use the MsgBoxViewer tool output to determine which tables are the largest and have the most records. The following table lists the BizTalk Server tables that typically grow the largest. You can use this data to determine where a potential problem may exist.

Table	Description
HostNameQ_Suspended	This table contains a reference to messages in the Spool table that are associated with suspended instances for the particular host. This table is in the BizTalkMsgBoxDb database.
HostNameQ	This table contains a reference to messages in the Spool table that are associated with the particular host and are not suspended. This table is in the BizTalkMsgBoxDb database.
Spool Parts Fragments	These tables store actual message data in the BizTalkMsgBoxDb database.
Instances	This table stores all instances and their current status in the BizTalkMsgBoxDb database.
TrackingData_0_x	These four tables store the Business Activity Monitoring (BAM) tracked events in the BizTalkMsgBoxDb database for TDDS to move the events to the BAMPrimaryImport database.
TrackingData_1_x	These four tables store the tracked events in the BizTalkMsgBoxDb database for TDDS to move the events to the BizTalkDTADB database.
Tracking_Fragmentsx Tracking_Partsx Tracking_Spoolx	Two of each of these tables are in the BizTalkMsgBoxDb and BizTalkDTADb databases. One is online, and the other is offline. In BizTalk Server 2004 SP2 and in later versions, the TrackedMessages_Copy_BizTalkMsgBoxDb SQL Server Agent job moves tracked message bodies directly to these tables in the BizTalkDTADb database. In BizTalk Server 2004 Service Pack 1 (SP1) and in earlier versions of BizTalk Server 2004, the TrackedMessages_Copy_BizTalkMsgBoxDb SQL Server Agent job copies tracked message bodies into these tables in the BizTalkMsgBoxDb database. The TrackingSpool_Cleanup_BizTalkMsgBoxDb SQL Server Agent job purges the offline tables and makes the tables online while the job also takes the online tables offline.
dta_ServiceInstances	This table stores tracked events for service instances in the BizTalkDTADb database. If this table is large, the BizTalkDTADb database is probably large.
dta_DebugTrace	This table stores the Orchestration debugger events in the BizTalkDTADb database.
dta_MessageInOutEvents	This table stores tracked event messages in the BizTalkDTADb database. These tracked event messages include message context information.
dta_ServiceInstanceExceptions	This table stores error information for any suspended service instance in the BizTalkDTADb database.

Consider the following scenarios.

HostNameQ_Suspended tables

If the HostNameQ_Suspended tables have many records, the tables could be valid suspended instances that appear in Group Hub or in HAT. These instances can be terminated. If these instances do not appear in Group Hub or in HAT, the instances are probably caching instances or orphaned routing failure reports. When suspended instances are terminated, the items in this table and their associated rows in the Spool and Instances tables are cleaned up.

In this scenario, handle the suspended instances by resuming them or terminating them. The BizTalk Terminator tool can also be used.

HostNameQ tables

If the HostNameQ tables have many of records, the following kinds of instances may exist:

• Ready-to-run instances
• Active instances
• Dehydrated instances

BizTalk Server needs time to "catch up" and process the instances. This table can grow when the incoming rate of processing outpaces the outgoing rate of processing. This scenario can occur when another problem occurs, such as a large BizTalkDTADb database or SQL Server disk delays.

Spool, Parts, and Fragments tables

If the Spool, Parts, and Fragments tables have many records, many messages are currently active, dehydrated, or suspended. Depending on the size, the number of parts, and the fragmentation settings in these tables, a single message may spawn all these tables. Each message has exactly one row in the Spool table and at least one row in the Parts table.

Instances table

The BizTalk Administrator should not allow for many suspended instances to remain in the Instances table. Dehydrated instances should only remain if the business logic requires long-running orchestrations. Remember that one service instance can be associated with many messages in the Spool table.

TrackingData_x_x tables

If the TrackingData_x_x tables are large, the Tracking host (TDDS) is not running or is not running successfully. If the tracking host instance is running, review the event logs and the TDDS_FailedTrackingData table in the BizTalkDTADb database for error information. If BizTalk is throttling with a state of 6 (large database), these tables can also be truncated by using the BizTalk Terminator tool.

If there is a large gap between the sequence numbers in the BizTalkMsgBoxDb TrackingData_x_x tables and the BAMPrimaryImport or BizTalkDTADb TDDS_StreamStatus tables, then TDDS may not move the data from the BizTalkMsgBoxDb database. To correct this, use the BizTalk Terminator tool to purge these tables and reset the sequence number.

On BizTalk Server 2006 R2, install BizTalk 2006 R2 Service Pack 1 to address a known issue with the tracking data. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

969870 FIX: The tracking data is not moved as expected from the BizTalkMsgBoxDb database to the BizTalkDTADb database in BizTalk Server 2006 R2

974563 List of Microsoft BizTalk Server hotfixes that are included in BizTalk Server 2006 R2 Service Pack 1

Tracking_Spool1 or Tracking_Spool2 tables

If the Tracking_Spool1 or Tracking_Spool2 tables become large in BizTalk Server 2004 SP1 and in earlier versions of BizTalk Server 2004, confirm that the TrackingSpool_Cleanup_BizTalkMsgBoxDb SQL Server Agent job is enabled and running.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

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

For more information about a Database Maintenance SDK sample, visit the following MSDN website:

http://msdn.microsoft.com/en-us/library/ms966372.aspx

dta_DebugTrace table and dta_MessageInOutEvents

The dta_DebugTrace table is populated when Shape start and end is enabled on an orchestration. If the dta_DebugTrace table has many records, these orchestration debugging events are being used or were being used. If orchestration debugging is not required for regular operations, clear the check box for the Shape start and end option in the orchestration properties.

The dta_MessageInOutEvents table is populated when Message send and receive is enabled on orchestrations and/or pipelines. If these tracking events are not needed, clear the check box for this option in the orchestration and/or pipeline properties.

If these trace events are disabled or if a backlog exists in the BizTalkMsgBoxDb database, these tables may continue to grow because TDDS continues to move this data into these tables.

By default, global tracking is enabled. If global tracking is not necessary, it can be disabled. For more information, visit the following Microsoft website:

http://technet.microsoft.com/en-us/library/bb203858.aspx

If the dta_DebugTrace table and/or the dta_messageInOutEvents table in the BizTalkDTADb database are too large, you can truncate the tables manually after you stop the tracking host. The BizTalk Terminator tool also provides this functionality.

In BizTalk Server 2004, the dtav_FindMessageFacts view in the BizTalkDTADb database prevents the dta_MessageInOutEvents table from truncating. To work around this behavior, follow these steps:

1. Stop the tracking host and the DTA Purge and Archive job.
2. If you want to truncate the dta_messageInOutEvents table, save and then delete the dtav_FindMessageFacts view. To do this, follow these steps:
   1. In SQL Server, access the dtav_FindMessageFacts view in the BizTalkDTADb database.
   2. Right-click the dtav_FindMessageFacts view, click All Tasks, and then click Generate SQL Script. When theGenerate SQL Scripts dialog box opens, make no changes, and then click OK.
   3. Name the file dtav_FindMessageFacts.sql, and then click Save.
   4. Right-click the dtav_FindMessageFacts view, and then click Delete. Click Drop All.

You can now truncate the table(s). If you truncate the dta_messageInOutEvents table, you must also truncate the dta_url table. The dta_url table only exists in BizTalk Server 2004.

When you are finished, follow these steps to re-create the dtav_FindMessageFacts view:

1. Open a new query in SQL Server.
2. In the Available Databases list, select the BizTalkDTADb database.
3. Execute your saved dtav_FindMessageFacts.sql script. This will re-create the view in the BizTalkDTADb database.

Restart the tracking host and the DTA Purge and Archive job.

Back to the top | Give Feedback

For more information about tracking database sizing guidelines, visit the following MSDN website:

http://msdn.microsoft.com/en-us/library/aa559162.aspx

dta_ServiceInstanceExceptions table

The dta_ServiceInstanceExceptions table typically becomes large in an environment that regularly has suspended instances.

Step 3: Investigate deadlock scenarios

In a deadlock scenario, enable DBCC tracing on the SQL Server so that the deadlock information is written to the SQLERROR log.

In SQL Server 2005 and later versions, execute the following statement:

DBCC TRACEON (1222,-1)

In SQL Server 2000, execute the following statement:

DBCC TRACEON (1204)

Additionally, use the PSSDiag utility to collect data on the Lock:Deadlock event and theLock:Deadlock Chain event.

The BizTalkMsgBoxDB database is a high-volume and high-transaction Online Transaction Processing (OLTP) database. Some deadlocking is expected, and this deadlocking is handled internally by the BizTalk Server engine. When this behavior occurs, no errors are listed in the error logs. When you investigate a deadlock scenario, the deadlock that you are investigating in the output must be correlated with a deadlock error in the event logs.

For more information about PSSDiag for SQL, click the following article number to view the article in the Microsoft Knowledge Base:

830232 PSSDIAG data collection utility

Back to the top | Give Feedback

Step 4: Look for blocked processes

Use Activity Monitor in SQL Server to obtain the server process identifier (SPID) of a locking system process. Then, run SQL Profiler to determine the SQL statement that is executing in the locking SPID.

To troubleshoot a locking and blocking issue in SQL Server, use the PSSDiag for SQL utility to capture all the Transact-SQL events that have the blocking script enabled.

In SQL Server 2005 and later versions, you can specify the blocked process threshold setting to determine which SPID or SPIDs are blocking longer than the threshold that you specify.

For more information about PSSDiag for SQL, click the following article number to view the article in the Microsoft Knowledge Base:

830232 PSSDIAG data collection utility

For more information about the blocked process threshold, visit the following MSDN website:

http://msdn2.microsoft.com/en-us/library/ms181150.aspx

Note When you experience a locking or blocking issue in SQL Server, we recommend that you contact Microsoft Customer Support Services. Microsoft Customer Support Services can help you configure the correct PSSDiag utility options.

Step 5: Install the Latest BizTalk Server Service Pack and Cumulative Update

BizTalk Server 2006 R2 and later versions have moved to a Cumulative Update (CU) model. The cumulative updates will contain the latest hot fixes. BizTalk Server 2006 R2 Service Pack 1 is also available:

BizTalk Server 2006 R2 Service Pack 1

BizTalk Server 2004 SP1 has no built-in purging and archiving functionality for the BizTalkDTADb database. This functionality is included with BizTalk Server 2004 SP2. Depending on the size of the BizTalkDTADb database, installing BizTalk Server 2004 SP2 may take hours because the Setup program purges the BizTalkDTADb database.

For information about the known issues when you install BizTalk Server 2004 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:

940519 Known issues in BizTalk Server 2004 Service Pack 2 that are not documented in the ReadmeSP2.htm file

Back to the top | Give Feedback

When you install BizTalk Server 2004 SP2, we recommend that you follow these steps:

1. Install the hotfix in Microsoft Knowledge Base article 894253. Follow the steps in this Knowledge Base article to execute the bts_tracking_shrinkexistingdatabase.sql script in SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
   894253 FIX: The dtasp_PruneTrackingdatabase() stored procedure may take many hours to clean up the DTA database in BizTalk Server 2004
2. Install BizTalk Server 2004 SP2. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
   888751 How to obtain the latest BizTalk Server 2004 service pack

Delete all the data

If the databases are too large or if the preferred method is to delete all data, all the data can be deleted.

Caution Do not use this method in any environment where the data is business critical or if the data is needed.

BizTalkMsgBoxDb Database Purging Steps

To delete all data in the BizTalkMsgBoxDb database, you can use the BizTalk Terminator tool. Otherwise, follow these steps.

Note This action deletes all messages. Be extremely cautious if you follow these steps in a production environment.

1. Back up all BizTalk Server databases. Remember, the BizTalkMgmtDb.dbo.sp_ForceFullBackup stored procedure can be used to force a full backup of the data and log files. Execute this stored procedure, and then execute the Backup BizTalk Server SQL Agent job.
2. Copy the Msgbox_cleanup_logic.sql script from Drive:\Program Files\Microsoft BizTalk 200x\schema to the SQL Server.
3. Execute this SQL script against the BizTalkMsgBoxDb database to update the bts_CleanupMsgbox stored procedure.
4. Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
5. Execute the bts_CleanupMsgbox stored procedure on all the BizTalkMsgBoxDb databases.
6. Restart all host instances and BizTalk Server services.

For information about a known issue with the bts_CleanupMsgbox stored procedure in BizTalk Server 2006, click the following article number to view the article in the Microsoft Knowledge Base:

924715 FIX: Message data is not deleted from the tracking database after you run the bts_CleanupMsgbox stored procedure in a BizTalk Server 2006 test environment

BizTalkDTADb database purging options

To delete all data from the BizTalkDTADb database, you can use the the BizTalk Terminator tool. Otherwise, use one of the following methods.

Note Both methods delete all messages. Method 2 is faster.

• Method 1:
  1. Back up all BizTalk Server databases.
  2. Execute the dtasp_PurgeAllCompletedTrackingData stored procedure. For more information about the dtasp_PurgeAllCompletedTrackingData stored procedure, visit the following MSDN website:
     http://msdn.microsoft.com/en-us/library/aa561918.aspx
     Note This action deletes all completed messages.
• Method 2:
  1. Back up all BizTalk databases.
  2. Execute the dtasp_CleanHMData stored procedure. Only use this option if the BizTalkDTADb database contains many incomplete instances that must be removed.
     
     To do this, follow these steps:
     1. Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
     2. Execute the dtasp_CleanHMData stored procedure on the BizTalkDTADb database.
     3. Restart all hosts and BizTalk Server services.

BizTalk Server 2004-only steps

To delete all data from the BizTalkDTADb database in BizTalk Server 2004, follow these steps.

Note This action deletes all completed messages.

1. Back up all BizTalk Server databases.
2. Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
3. Install the hotfix in Microsoft Knowledge Base article 894253. Follow the steps in this Knowledge Base article to run the Bts_tracking_shrinkexistingdatabase.sql script in SQL Server 2000.
   
   For information about the dtasp_PruneTrackingdatabase stored procedure, click the following article number to view the article in the Microsoft Knowledge Base:
   894253 FIX: The dtasp_PruneTrackingdatabase() stored procedure may take many hours to clean up the DTA database in BizTalk Server 2004
4. Restart all hosts and BizTalk services.

Note If you must have the tracking data, back up the BizTalkDTADb database, restore the database to another SQL Server, and then purge the original BizTalkDTADb database.

If you need help to analyze the MsgBoxViewer data or the PSSDiag output, contact Microsoft Customer Support Services. For a complete list of Customer Support Services telephone numbers and information about support costs, visit the following Microsoft website:

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

Note Before you contact Customer Support Services, compress the MsgBoxViewer data, the PSSDiag output, and the updated event logs (.evt files). You may have to send these files to a BizTalk Server support engineer.