Export (0) Print
Expand All

Checklist: Maintaining and Troubleshooting BizTalk Server Databases

BizTalk Server databases and their health are very important for a successful BizTalk Server database messaging environment. This topic lists the steps that you must follow when maintaining or troubleshooting the BizTalk Server databases.

Steps Reference

Disable the Auto Update Statistics and Auto Create Statistics Options (applicable only to BizTalk Server MessageBox databases).

Gg634509.note(en-us,BTS.70).gifNote
These settings are done by default as part of the BizTalk Server configuration. You should not make changes to these settings.

You must disable the Auto Create Statistics and the Auto Update Statistics options. To determine if 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'

The value returned for CurrentSetting should be OFF. If this value returned for CurrentSetting is ON, change it to 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 about these settings see the following Microsoft Knowledge Base articles:

Set the Max Degree of Parallelism property

Gg634509.note(en-us,BTS.70).gifNote
These settings are done by default as part of the BizTalk Server configuration. You should not make changes to these settings.

Set the Max Degree of Parallelism run_value and config_value properties to a value of one (1) on the SQL Server instances that host the BizTalk Server Messagebox databases. To check 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 are not set to a value of one (1), execute the following stored procedure in SQL Server:

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

For more information about how the Max Degree of Parallelism setting affects BizTalk Server, see the following Microsoft Knowledge Base articles:

Determine when you can rebuild BizTalk Server indexes

Most of the indexes in BizTalk Server databases are clustered (index ID: 1). The DBCC SHOWCONTIG command can be used to display fragmentation information for tables in the BizTalk Server databases. These indexes are GUID-based so it is normal for fragmentation to occur. If the Scan Density value of DBCC SHOWCONTIG is less than 30%, the indexes can be rebuilt during downtime. Many tables in the BizTalk Server databases contain columns that use DataType definitions where online indexing cannot be done. Therefore, the indexes for tables in the BizTalk Server databases should never be rebuilt while BizTalk is processing data. For more information on how to rebuild the BizTalk indexes, see the following Microsoft Knowledge Base article:

For more information about index fragmentation and workload types, see the white paper Microsoft SQL Server 2000 Index Defragmentation Best Practices (http://go.microsoft.com/fwlink/?LinkId=101580).

Gg634509.note(en-us,BTS.70).gifNote
You can also use the sys.dm_db_index_physical_stats function to look for fragmentation information in SQL Server 2005 and SQL Server 2008 SP1. For more information, see sys.dm_db_index_physical_stats (Transact-SQL) (http://go.microsoft.com/fwlink/?LinkId=158493).

Monitor the database for locks, blocks, or deadlocks

It is an expected behavior for locks and blocks to occur on the SQL Server databases used by BizTalk Server. However, it is not expected to have these locks or blocks to continue for an extended period of time. Extended blocking and deadlocking on the SQL Server databases used by BizTalk Server are indicators of a potential problem. For the current known causes of deadlocking and blocking on the SQL Server databases used by BizTalk Server, review the following Microsoft Knowledge Base article:

Monitor the size of databases and tables

The size of the BizTalk Server Messagebox database should typically be no more than approximately 5GB. The BizTalkMsgBoxDb database should not be holding any data, and should be considered a buffer until the data is processed or moved to the BizTalkDTADb database. An environment with a powerful SQL Server backend and numerous long running orchestrations may have a BizTalkMsgBoxDb database larger than 5GB. A high volume environment with no long-running orchestrations should have a BizTalk Server Messagebox database much smaller than 5GB. The BizTalk Server tracking database can vary greatly in size but if query performance decreases dramatically, then the tracking database is probably too large. As a rule of thumb, a BizTalk Server tracking database larger than 15-20 GB is considered too large and may adversely impact performance. The following issues may be attributable to BizTalk Server databases that are too large:

  • The BizTalk Server Messagebox database continues to grow while the data size (not just the log file) remains large. BizTalk Server takes a longer time than normal to process even a simple message flow scenario.

  • Group Hub queries take a longer time than normal and may even timeout.

  • The database log file never gets truncated.

  • The BizTalk SQL Agent jobs run slower than normal.

  • Some tables are considerably large or have too many rows compared to normal.

The BizTalk Server databases can become large for several reasons including:

  • BizTalk SQL Agent Jobs not running

  • Excessive suspended message or service instances

  • Disk failures

  • High levels of tracking

  • BizTalk Server throttling

  • Poor SQL Server performance

  • Network latency issues

Similarly, you can have 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.

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

Enable tracking on BizTalk Server host

By default, tracking is enabled on the default host. BizTalk requires 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 BizTalk Server Messagebox database to the BizTalk Server tracking database. If no BizTalk Server hosts are configured with the option to Allow Host Tracking or if the tracking host is stopped, then TDDS will not run and the TrackingData_x_x tables in the BizTalk Server Messagebox database will grow unchecked. Therefore, a dedicated BizTalk Server host should be configured with the option to Allow Host Tracking. For more information about configuring a dedicated tracking host see Configuring a Dedicated Tracking Host.

Gg634509.note(en-us,BTS.70).gifNote
To allow TDDS to maintain new tracking events in high volume scenarios, you can create multiple instances of a single tracking host but no more than one host should be configured for tracking.

Use the correct BizTalk SQL Server Agent jobs

Execution of the BizTalk Server SQL Agent jobs are crucial for managing the BizTalk Server databases and for maintaining optimal performance.

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

    Gg634509.note(en-us,BTS.70).gifNote
    For more information about using the SQL Server full recovery model when configuring the SQL Agent Backup BizTalk Server job, see Log Shipping (http://go.microsoft.com/fwlink/?LinkId=153450) or Backup Under the Full Recovery Model (http://go.microsoft.com/fwlink/?LinkId=156509).

  • The MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job is designed to run indefinitely. As a result the SQL Agent job history may not indicate that this SQL Agent job has successfully completed; this behavior is by design. If there is a failure, the job will restart within 1 minute and continue running unabated. Therefore, failure notifications for this job can typically be ignored. If the job history for the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job indicates that this job is constantly failing and restarting then further investigation into the cause of the failure/restart cycle may be required.

  • The MessageBox_Message_Cleanup_BizTalkMsgBoxDb SQL Server Agent job is the only job that should not be manually enabled because it is initiated by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job.

  • The DTA Purge and Archive SQL Server Agent job maintains the BizTalk Server tracking database by purging and archiving tracked messages. This job reads every row in the table and compares the timestamp of each row to determine if the record should be removed.

Gg634509.note(en-us,BTS.70).gifNote
When troubleshooting the BizTalk Server SQL Server Agent jobs, verify that all SQL Agent jobs except the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb are completing without errors.

For more information about the BizTalk Server 2010 SQL Agent Jobs used in SQL Server:

Monitor and terminate suspended instances

Service instances can be suspended (resumable) or suspended (not resumable). These service instances may be Messaging, Orchestration, or Port. BizTalk Server 2010 accommodates termination and removal of these instances by using the Group Hub page in the BizTalk Server Administration Console or through the use of the Terminate.vbs script. For more information about the Terminate.vbs script, see Removing Suspended Service Instance (http://go.microsoft.com/fwlink/?LinkId=153453).

Gg634509.note(en-us,BTS.70).gifTip
You can also use the Terminator tool to remove suspended instances. The Terminator tool is available at http://go.microsoft.com/fwlink/?LinkId=151931. Use of this tool is not supported by Microsoft, and Microsoft makes no guarantees about the suitability of this programs. Use of this program is entirely at your own risk.

The terms "orphans” and "zombies" are often used interchangeably. An orphaned or zombie message is a message that does not have an associated service instance, typically because the service instance has terminated before the message was received. An orphaned or zombie service is a service that does not have any associated messages. For more information about zombie messages and service instances in BizTalk Server see Zombies in BizTalk Server (http://go.microsoft.com/fwlink/?LinkId=153454).

Monitor the performance counters of the PhysicalDisk performance object

BizTalk Server makes a large number of short, very quick transactions to SQL Server within one minute. If the SQL Server cannot sustain this activity, you may experience BizTalk Server performance issues. 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.

You can also refer to the following Microsoft Knowledge Base articles for more information about SQL Server performance:

Follow best practices for BizTalk Server databases.

See Best Practices for Maintaining BizTalk Server Databases.

Perform the following tasks to troubleshoot any issues with BizTalk Server databases.

Steps Reference

Ensure all required BizTalk SQL Server Agent jobs are enabled and running

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.

Use the MsgBoxViewer tool to analyze the BizTalk MessageBox and other databases

Gg634509.Important(en-us,BTS.70).gifImportant
Use of this tool is not supported by Microsoft, and Microsoft makes no guarantees about the suitability of this programs. Use of this program is entirely at your own risk.

The MsgBoxViewer tool is available from http://go.microsoft.com/fwlink/?LinkId=151930 (http://go.microsoft.com/fwlink/?LinkId=151930). 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.

When BizTalk Server is running slower than usual, run the MsgBoxViewer tool, click to select all queries on the Optional Queries tab, and then review the generated HTML report for any problems. The Summary Report section lists warnings in yellow and potential problems in red.

Additionally, you can use the MsgBoxViewer tool to determine which tables are the largest and have the most records. For a list of tables that typically grow the larges and for instructions on how to manage those tables, see Large-growing BizTalk Server Database Tables.

Use the Terminator tool to resolve issues, if any, identified by the MsgBoxViewer tool

Gg634509.Important(en-us,BTS.70).gifImportant
Use of this tool is not supported by Microsoft, and Microsoft makes no guarantees about the suitability of this programs. Use of this program is entirely at your own risk.

Run the Terminator tool available at Terminator (http://go.microsoft.com/fwlink/?LinkId=151931). This tool enables users to easily resolve any issues identified by the BizTalk MsgBoxViewer tool. For more information on how the Terminator tool integrates with the BizTalk MsgBoxViewer tool, see Using BizTalk Terminator to resolve issues identified by BizTalk MsgBoxViewer (http://go.microsoft.com/fwlink/?LinkId=151932).

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 2008 or SQL Server 2005, execute the following statement to enable DBCC tracing for deadlock scenarios:

DBCC TRACEON (1222,-1)

You can also use the PSSDIAG utility to collect data on the Lock:Deadlock event and the Lock:Deadlock Chain event. For more information about the PSSDIAG utility, see PSSDIAG data collection utility (http://go.microsoft.com/fwlink/?LinkId=153627).

The BizTalkMsgBoxDB database is a high-volume and high-transaction Online Transaction Processing (OLTP) database. With such databases, 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.

Look for blocked processes

You can use Activity Monitor in SQL Server to obtain the server process identifier (SPID) of a locking system process. You can then run the SQL Profiler to determine the SQL statement that is executing in the locking SPID. You can use the PSSDIAG utility to troubleshoot locking and blocking issues in SQL Server. The utility captures all the Transact-SQL events that have the blocking script enabled. For more information about the PSSDIAG utility, see PSSDIAG data collection utility (http://go.microsoft.com/fwlink/?LinkId=153627).

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 the blocked process threshold option, see blocked process threshold Option (http://go.microsoft.com/fwlink/?LinkId=153628).

Gg634509.note(en-us,BTS.70).gifNote
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.

Delete all unwanted data

If the databases have grown to become too large and if the data contained in the databases will not be required any longer, the preferred method is to delete the data.

Gg634509.Caution(en-us,BTS.70).gifCaution
Do not use this method in any environment where the data is business critical or if the data is needed.

To purge the BizTalkMsgBox database

  1. Download the Msgbox_cleanup_logic.sql script from Microsoft Knowledge Base article 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 (http://go.microsoft.com/fwlink/?LinkId=153630).

  2. Back up all BizTalk Server databases.

  3. Copy the Msgbox_cleanup_logic.sql script to the computer hosting the SQL Server.

  4. Execute this SQL script against the BizTalkMsgBoxDb database to update the bts_CleanupMsgbox stored procedure.

  5. Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.

  6. Execute the bts_CleanupMsgbox stored procedure on all the BizTalkMsgBoxDb databases.

    Gg634509.Caution(en-us,BTS.70).gifCaution
    Do not run the bts_CleanupMsgbox stored procedure on a production server that is running BizTalk Server. You should only run the bts_CleanupMsgbox stored procedure in a test environment. Running the bts_CleanupMsgbox stored procedure in a production environment is not supported.

  7. Restart all hosts and BizTalk Server services.

To purge the BizTalkDTADb database

  • Method 1

    1. Back up all BizTalk Server databases.

    2. Execute the dtasp_PurgeAllCompletedTrackingData stored procedure. For more information about the stored procedure, see How to Manually Purge Data from the BizTalk Tracking Database (http://go.microsoft.com/fwlink/?LinkId=153635).

  • Method 2. Use this option only if the BizTalkDTADb database contains many incomplete instances that must be removed.

    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. Execute the dtasp_CleanHMData stored procedure on the BizTalkDTADb database.

    4. Restart all hosts and BizTalk Server services.

Gg634509.note(en-us,BTS.70).gifNote
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 want help analyzing the MsgBoxViewer data or PSSDIAG output, contact Microsoft Customer Support Services. 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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft