Export (0) Print
Expand All
0 out of 1 rated this helpful - Rate this topic

Troubleshooting SQL Server

The majority of Microsoft SQL Server issues that affect Microsoft BizTalk Server fall into one of the following categories:

  • Connectivity-related problems

  • Permissions-related problems

  • Database-sizing problems

This topic discusses each of these categories and steps that you can take to resolve the associated problems.

Connectivity-Related Problems

The following issues are most commonly associated with connectivity problems between the BizTalk Server computer and the SQL Server computer that houses the BizTalk Server databases.

Errors related to failed transactions or "Communication with the underlying transaction manager" errors are written to the BizTalk Server Application log

Problem

Errors indicating an MSDTC transaction failure or a failure to communicate with the underlying transaction manager are written to the BizTalk Server Application log.

Cause

MSDTC connectivity between BizTalk ServerandSQL Server has failed.

Resolution

For information about troubleshooting MSDTC connectivity between the BizTalk Server computer and the SQL Server computer that houses the BizTalk Server databases, see Troubleshooting Problems with MSDTC.

Error "A connection was successfully established with the server, but then an error occurred during the pre-login handshake" occurs when connecting to remote SQL Server databases on SQL Server 2008

Problem

BizTalk Server loses connectivity with a remote SQL Server computer that houses the BizTalk Server databases and an error message is generated:

Cause

This problem may occur if one or more of the following conditions is true:

  • SQL Server is not configured to accept remote connections.

  • The necessary protocols for SQL Server are not enabled on either the SQL Server computer or the SQL Server client computer that is running BizTalk Server.

Resolution

Follow these steps to resolve this problem:

  • The SQL Server Surface Area Configuration tool is not available on SQL Server 2008. To enable remote connections for SQL Server on a SQL Server 2008 computer follow the instructions in the SQL Server 2008 online help.

  • Use the SQL Server Configuration Manager tool to enable the TCP/IP and/or the Named Pipes protocols on the SQL Server computer.

    1. Click Start, point to All Programs, and click SQL Server Configuration Manager.

    2. Click to expand SQL Server Network Configuration and then click Protocols for MSSQLSERVER.

    3. Right-click the TCP/IP protocol and then click Enable.

    4. Right-click the Named Pipes protocol and then click Enable.

    5. Close the SQL Server Configuration Manager tool.

  • Use the SQL Server Configuration Manager tool to enable the TCP/IP and/or the Named Pipes protocols on the SQL Server client computer that is running BizTalk Server.

    1. Click Start, point to All Programs, and click SQL Server Configuration Manager.

    2. Click to expand SQL Server Network Configuration and then click ClientProtocols.

    3. Right-click the TCP/IP protocol and then click Enable.

    4. Right-click the Named Pipes protocol and then click Enable.

    5. Close the SQL Server Configuration Manager tool.

    noteNote
    Ensure that at least one of the protocols on the SQL Server client computer that is running BizTalk Server matches the protocols enabled on the SQL Server computer.

A BizTalk host instance fails and a "General Network" error is written to the Application log when the BizTalk Server-based server processes a high volume of documents

Problem

When processing a high volume of documents, a BizTalk host instance fails, and a "General Network" error is written to the Application log.

Cause

This issue occurs because Microsoft Windows Server 2008 SP2 implements a security feature that reduces the size of the queue for concurrent TCP/IP connections to the server. This feature helps prevent denial of service attacks.

Resolution

For more information about resolving this issue, see Avoiding DBNETLIB Exceptions.

Permissions-Related Problems

BizTalk Server run-time or design-time operations fail and a "cannot open database requested in login <database>" error is written to the Application log of the BizTalk Server or SQL Server computer

Problem

A run-time or design-time operation fails and an error similar to the following is written to the application log of the BizTalk Server or SQL Server computer:

Cannot open database requested in login <database>. Login fails.
Login failed for user <username>.

Cause

This error can occur if the specified account does not belong to the appropriate Windows group or SQL Server role.

Resolution

Ensure that the specified account is a member of the appropriate Windows group or SQL Server role. For more information about the appropriate memberships, see Windows Groups and User Accounts in BizTalk Server.

Database-Sizing Problems

If the BizTalk Server databases grow unchecked then the performance of the BizTalk Server environment will be adversely affected. Follow the steps below to manage the growth of the BizTalk Server databases.

The BizTalk Server MessageBox database is growing unchecked and impacting overall performance

Problem

Growth of the BizTalk Server MessageBox database is adversely affecting performance of the BizTalk Server environment.

Cause

This problem can occur if the SQL Agent jobs that maintain the BizTalk Server databases are not running.

Resolution

Ensure that the SQL Agent jobs that maintain the BizTalk Server databases are running. See Database Structure and Jobs for a complete list of the SQL Agent jobs that are installed with BizTalk Server.

The BizTalk Server tracking database is growing unchecked and impacting overall performance

Problem

The BizTalk Server tracking database is growing unchecked and is adversely affecting the overall performance of the BizTalk Server environment.

Cause

This problem can occur if steps are not taken to purge and archive the BizTalk Server tracking database.

Resolution

Steps should be taken to purge and archive the BizTalk Server tracking database. See Archiving and Purging the BizTalk Tracking Database for more information.

See Also

© 2010 Microsoft Corporation. All rights reserved.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.