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.
Click Start, point to All Programs, and click SQL Server Configuration Manager.
Click to expand SQL Server Network Configuration and then click Protocols for MSSQLSERVER.
Right-click the TCP/IP protocol and then click Enable.
Right-click the Named Pipes protocol and then click Enable.
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.
Click Start, point to All Programs, and click SQL Server Configuration Manager.
Click to expand SQL Server Network Configuration and then click ClientProtocols.
Right-click the TCP/IP protocol and then click Enable.
Right-click the Named Pipes protocol and then click Enable.
Close the SQL Server Configuration Manager tool.
Note
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.