If SQL Server is hit with multiple and simultaneous TCP/IP
connection requests, such as the case with World Wide Web servers, the requests
are quickly rejected by responding with a TCP/IP Reset Frame.
symptom is difficult to detect. If the client application includes error code
handling, the native error code is 10061 (WSAECONNREFUSED) for the Open()
function. On the server, Error 17832 - "Unable to read login packet" appears in
the SQL Error log.
If you trace this problem with a protocol
analyzer, you can see that some of the connection request frames have been
replied to with the TCP Reset bit set, which tells the sending station that the
frame has been received, but the server does not have the resources to process
the connection request.
A Winsock application accepts connections on a port by
calling a Listen() function, which has a backlog parameter specifying the
maximum length of the pending-connection queue. The Winsock specification
defines the maximum Listen() backlog at five and when it exceeds five, TCP/IP
issues a reset.
The backlog for SQL Server can be configured to
handle a great number of pending connections by modifying the Windows NT
Registry. In some cases, the connection reset issue is resolved due to the
This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
How to back up and restore the registry in Windows
- Start Registry Editor (REGEDT32.EXE) and locate the
- The corresponding registry subkey in the
HKEY_LOCAL_MACHINE subtree for SQL 4.21a is:
- If SQL Server 6.5 Enterprise Edition is running with a
Virtual SQL Server in a clustered environment, you must make the change to
virtual server key, rather than the standard:
\Software\Microsoft\MSSQLServer\MSSQLServer This virtual server key is:
Value Name: WinsockListenBacklog
- The new registry location for SQL Server
For a default instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib For a named instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\SuperSocketNetLib
- Select the MSSQLServer subkey for SQL Server 6.5 and SQL Server 7.0, and select the SuperSocketNetLib for SQL Server 2000. On the Edit menu, click Add Value.
- Enter the following:
Value Name: WinsockListenBacklogNote The data value affects overall system resources if it is set to
a high value. Windows NT 3.51 has a maximum overall backlog of 100 and Windows
NT 4.0 has a maximum overall backlog of 200 for all applications. The suggested
method of testing with this value is to set the value in increments of five and
observe the results until the connection reset stops.
Data Type: REG_DWORD
Data: Range is 1 to 0xFFFFFFFF
- Click OK and quit Registry Editor.
- Restart SQL Server.
For more information about the Listen()
function, see the WIN32 SDK Programmer's reference.
For more information about the backlog parameter and Windows NT, click the following article number to view the article in the Microsoft Knowledge Base:
WinSocket applications reject connection requests with Reset frames
Windows NT WinSock Listen (Backlog) parameter limit