Export (0) Print
Expand All

Maintaining BizTalk Server Databases

BizTalk Server database databases and their health are very important for a successful BizTalk Server database messaging environment. This topic discusses important things to consider when you are working with BizTalk Server database databases. These considerations include the following:

  • Auto Update Statistics/Auto Create Statistics SQL Server settings

  • Max Degree of Parallelism setting on SQL server

  • Indexing

  • Locking/Deadlock/Blocking

  • Large Databases or Tables

  • BizTalk SQL Agent Jobs

  • Suspended Instances

This topic describes techniques that can be used to maintain BizTalk Server databases and how to troubleshoot BizTalk Server database issues related to BizTalk Server database size.

Auto Update Statistics/Auto Create Statistics

The Auto Create Statistics and the Auto Update Statistics options should be disabled on the BizTalk Server Messagebox database. 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 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:

917845 - "You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server 2006 or in BizTalk Server 2004", which is available at http://support.microsoft.com/kb/917845

912262 - "The auto update statistics option, the auto create statistics option, and the Parallelism setting are turned off in the SQL Server 2000 or SQL Server 2005 database instance that hosts the BizTalk Server 2004 or BizTalk Server 2006 BizTalkMsgBoxDB database", which is available at http://support.microsoft.com/kb/912262

Max Degree of Parallelism

The Max Degree of Parallelism run_value and config_value properties should be set to a value of one (1) on the SQL Server computer that hosts the BizTalk Server Messagebox database. 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:

899000 - "The Parallelism setting for the instance of SQL Server when you configure BizTalk Server", available at http://support.microsoft.com/kb/899000.

917845 - "You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server 2006 or in BizTalk Server 2004", available at http://support.microsoft.com/kb/917845.

Indexing

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:

917845 - "You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server 2006 or in BizTalk Server 2004", available at http://support.microsoft.com/kb/917845

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

Locking/Deadlocking/Blocking

It is expected behavior for locks and blocks to occur on the SQL Server databases used by BizTalk Server. It is not expected behavior for 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:

917845 - "You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server 2006 or in BizTalk Server 2004", available at http://support.microsoft.com/kb/917845.

Large Databases or Tables

The size of the BizTalk Server Messagebox database should typically be no more than approximately 5GB. 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 that 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.

  • Health and Activity Tracking (HAT) 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

When tracking is enabled on a BizTalk Server host, 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, 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.

Cc296892.note(en-US,BTS.10).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.

BizTalk SQL 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 job is the only supported method to backup the BizTalk Server databases and requires that all of the BizTalk Server databases are configured to use the SQL Server full recovery model. See BizTalk Server Log Shipping for more information about using the SQL Server full recovery model when configuring the SQL Agent Backup BizTalk Server job This job must be enabled to ensure a healthy BizTalk Server environment.

The MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL 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 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 Agent job is the only BizTalk job that should not be manually enabled because it is initiated by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job. The DTA Purge and Archive SQL 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. When troubleshooting the BizTalk Server SQL 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 2006 R2 SQL Agent Jobs used in SQL Server, see "Database Structure and Jobs" in BizTalk Server 2006 R2 Help at http://go.microsoft.com/fwlink/?LinkID=107543.

Suspended Instances

A proliferation of suspended message and service instances may cause the BizTalk Server Messagebox database to increase in size beyond what would be expected during normal operations. BizTalk Server 2006 and BizTalk Server 2006 R2 accommodate termination and removal of these instances by using the Group Hub page in the BizTalk Administration Console, through the Health and Activity Tracking (HAT) interface, or through the use of the Terminate.vbs script.

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 2006" in BizTalk Server 2006 R2 Help at http://go.microsoft.com/fwlink/?LinkId=108248

For more information on using the Terminate.vbs script to remove suspended instances see "Removing Suspended Service Instances" in BizTalk Server 2006 R2 Help at http://go.microsoft.com/fwlink/?LinkID=107591.

Another common cause of table growth in the BizTalk Server Messagebox database is the accumulation of caching instances which are not displayed in Group Hub and therefore cannot be suspended or terminated using the Group Hub in the BizTalk Server Administration console.

  • In certain scenarios, zombie messages can accumulate in cache service instances on BizTalk Server 2006. To resolve this issue, obtain and install the hotfix for Knowledge Base article 936536. To obtain this hotfix; complete the hotfix request web submission form at http://go.microsoft.com/?linkid=6294451.

  • When a BizTalk Server service terminates, caching instances may not get removed. To resolve this issue, obtain and install the hotfix for Microsoft Knowledge Base article 944426.To obtain this hotfix; complete the hotfix request web submission form at http://go.microsoft.com/?linkid=6294451.

Another common problem related to suspended instances is the accumulation of Routing Failure Reports (RFR’s) in the BizTalkHostQ and BizTalkHostQ_Suspended tables in the BizTalk Server Messagebox database. These routing failure reports are not removed by default, which may cause the BizTalk Server Messagebox database to increase in size. To resolve this issue on BizTalk Server 2006, obtain and install the hot fix for Microsoft Knowledge Base article 941690, "FIX: Routing failure reports are not removed from the <BizTalkHostName>Q_Suspended table on a BizTalk Server 2006 server". For more information about this hotfix see http://support.microsoft.com/kb/941690. To obtain this hotfix; complete the hotfix request web submission form at http://go.microsoft.com/?linkid=6294451.

Cc296892.note(en-US,BTS.10).gifNote
This issue is fixed in BizTalk Server 2006 R2 and does not require the installation of a hotfix.

Show:
© 2014 Microsoft