Export (0) Print
Expand All

Troubleshooting SQL Server

The majority of Microsoft SQL Server issues that affect Microsoft BizTalk Server 2006 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.

The following issues are most commonly associated with connectivity problems between the BizTalk Server 2006 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 Server 2006 and SQL Server has failed.

Resolution

For information about troubleshooting MSDTC connectivity between the BizTalk Server 2006 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 2005

Problem

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

Cause

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

  • SQL Server 2005 is not configured to accept remote connections.

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

Resolution

Follow these steps to resolve this problem:

  • Use the SQL Server Surface Area Configuration tool to enable remote connections for SQL Server 2005 on the SQL Server 2005 computer:

    1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.

    2. Click Surface Area Configuration for Services and Connections.

    3. Click to expand MSSQLSERVER, click to expand Database Engine, and then click Remote Connections.

    4. Click to select Local and Remote Connections, click to select Using both TCP/IP and named pipes, and then click OK.

    5. Close the SQL Server Surface Area Configuration tool.

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

    1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

    2. Click to expand SQL Server 2005 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 2005 client computer that is running BizTalk Server 2006.

    1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

    2. Click to expand SQL Server 2005 Network Configuration and then click Client Protocols.

    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.

    Aa952081.note(en-us,BTS.20).gifNote
    Ensure that at least one of the protocols on the SQL Server 2005 client computer that is running BizTalk Server 2006 matches the protocols enabled on the SQL Server 2005 computer.

EDI adapter configuration fails with "Could not connect to database" error

Problem

Configuration of the EDI adapter fails with an error similar to the following:

Could not connect to Database for EDI Subsystem (error=-44)

Cause

The EDI adapter requires that the Client Connectivity component for SQL Client Tools is installed on the BizTalk Server 2006 computer. If this component is not installed on the BizTalk Server 2006 computer then this error will occur.

Resolution

Install the Client Connectivity component for SQL Client Tools on the BizTalk Server 2006 computer. For more information see Known Issues with the EDI Adapter.

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 2003 Service Pack 1 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.

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 2006 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 2006.

If the BizTalk Server databases grow unchecked then the performance of the BizTalk Server 2006 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 2006 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 2006.

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 2006 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.

Community Additions

ADD
Show:
© 2014 Microsoft