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 (917845)


The information in this article applies to:

  • Microsoft BizTalk Server 2006 Standard Edition
  • Microsoft BizTalk Server 2006 Developer Edition
  • Microsoft BizTalk Server 2006 Enterprise Edition
  • Microsoft BizTalk Server 2004 Standard Edition
  • Microsoft BizTalk Server 2004 Developer Edition
  • Microsoft BizTalk Server 2004 Enterprise Edition
  • Microsoft BizTalk Server 2004 Partner Edition

SYMPTOMS

In Microsoft BizTalk Server 2006 or in Microsoft BizTalk Server 2004, you experience blocking, deadlock conditions, or other Microsoft SQL Server issues when you try to connect to the BizTalkMsgBoxDb database.

CAUSE

This issue may occur if one or more of the following conditions are true:
  • The Auto Create Statistics option is enabled on the BizTalkMsgBoxDb database.
  • The Auto Update Statistics option is enabled on the BizTalkMsgBoxDb database.
  • The max degree of parallelism option is set to a value other than 1 on the computer that is running SQL Server and that is hosting the BizTalkMsgBoxDb database.
  • You defragment or rebuild an index in the BizTalkMsgBoxDb database when BizTalk Server is processing data.

RESOLUTION

To resolve this issue, use one of the following methods.

Method 1: Disable the Auto Create Statistics option and the Auto Update Statistics option

To resolve this issue, disable the Auto Create Statistics option and the Auto Update Statistics option on the BizTalkMsgBoxDb database in SQL Server.

Method 2: Set the max degree of parallelism option to 1

To resolve this issue, set the max degree of parallelism option to 1 on the computer that is running SQL Server.

Method 3: Do not rebuild an index when BizTalk Server is processing data

To resolve this issue, do not run the bts_RebuildIndexes stored procedure or any SQL command that rebuilds an index in a BizTalk Server database when BizTalk Server is processing data.

Note Defragmenting an index in a BizTalk Server database is not supported.

MORE INFORMATION

The only supported method to rebuild an index in the BizTalkMsgBoxDb database is to run the bts_RebuildIndexes stored procedure. The BizTalkMsgBoxDb database is the only BizTalk Server database that includes a stored procedure to rebuild an index in the database.

You can use the DBCC DBREINDEX SQL command to rebuild an index in the other BizTalk Server databases. For an example of how to use the DBCC DBREINDEX SQL command, right-click the bts_RebuildIndexes stored procedure, and then click Properties.

Note Microsoft only supports rebuilding database indexes during BizTalk Server downtime. You should stop all data processing that is related to BizTalk Server before you rebuild an index.
Modification Type:MajorLast Reviewed:4/14/2006
Keywords:kbBTS kbtshoot kbprb KB917845 kbAudITPRO kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.