How to maintain and troubleshoot BizTalk Server databases

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

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

exec sp_configure 'max degree of parallelism'

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

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:

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:

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:For more information about BizTalk Server 2004 database high availability, visit the following MSDN website: For more information about BizTalk Server 2006 database high availability, visit the following MSDN website:For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

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: 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 the Backup 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:

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: For more information about throttling in BizTalk Server, visit the following MSDN website: 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. 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:

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:

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

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: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 the Generate 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.

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

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:In SQL Server 2000, execute the following statement: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:

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: For more information about the blocked process threshold, visit the following MSDN website: 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 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:

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

   (http://support.microsoft.com/default.aspx?scid=kb;EN-US;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

   (http://support.microsoft.com/default.aspx?scid=kb;EN-US;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:

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

     (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

   (http://support.microsoft.com/default.aspx?scid=kb;EN-US;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: 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.