INF: Multiple Named Pipes Connections May Cause Error 17832

• Microsoft SQL Server, versions 6.0 and 6.5

SUMMARY

When SQL Server receives many simultaneous Named Pipes connection requests, some connection requests may fail if the pipe is busy. In the SQL Server error log, multiple "17832 Unable to read login packet" errors are typically logged. On the client side, error 231 "connection busy" or "pipe busy" may be generated.

These errors may be generated when a client connection tries to open a pipe and there is no pipe instance available to service the next pipe open request. This is an expected behavior in an environment where SQL Server is hit by numerous simultaneous connection requests, as in the case of Microsoft Internet Information Server (IIS).

However, this behavior does not prevent you from implementing very large multi-user systems. In fact, Microsoft SQL Server is used in production with over 4,000 concurrent users connecting by means of named pipes to a single server.

MORE INFORMATION

There is only one thread that handles incoming SQL Server Named Pipes connection open requests. In an environment where multiple simultaneous named pipes connection requests are issued, it is very likely that no pipe instance is available to service the next pipe open request at any particular moment. In this case, a pipe busy status is returned to the client and the client retries the connection within the specified timeout period until the pipe is available. From a network sniffer trace, on the Windows NT Server Create response, error code (172) STATUS_PIPE_NOT_AVAILABLE is returned to the client. In case of a 16-bit client connection, code (231) ERROR_PIPE_BUSY is returned to the client.

In this situation, DB-Library and the ODBC driver issue a retry mechanism that retries after a delay that varies from 200 ms up to one second. The client may successfully connect during one of the retries, but it may also fail if the connection keeps receiving a pipe busy status in all the retries for the duration of the logon timeout value that is set on the client.

The "17832 Unable to read login packet" errors are more noticeable if you have an ODBC front end application. This error is generated because the initial session between the client and the server has been established but the client timed out before the login packet was sent to the server.

The following suggestions can help reduce the occurrences of these errors:

• Increase the application logon timeout value. In this interval, DB-Library and the ODBC driver manager will retry connecting to SQL Server, thereby increasing the chance of a successful connection.
• Configure SQL Server to listen on multiple pipes. Doing this will increase the number of threads available to process incoming Named Pipes connections. Different client workstations must be configured to use alternate pipes to evenly distribute the load on each pipe.

  You can configure SQL Server to listen on multiple pipes by using the registry editor.

  WARNING: Using Registry Editor incorrectly can cause serious, system- wide problems that may require you to reinstall Windows NT to correct them. Microsoft cannot guarantee that any problems resulting from the use of Registry Editor can be solved. Use this tool at your own risk.

  1. Start Registry Editor (Regedt32.exe) and locate the following subkey

        in the HKEY_LOCAL_MACHINE subtree:
  

           Software\Microsoft\MSSQLServer\MSSQLServer
  
     2. Double-click the ListenOn value.
  
     3. On a new line in the Multi-string editor window, add the following:
  
           SSNMPN60,\\.\pipe\sql1\query
           SSNMPN60,\\.\pipe\sql2\query
  
     4. Click OK and quit Registry Editor.
  
     5. Shut down and restart SQL Server.
  
     You can configure the client to connect to SQL server using an alternate
     pipe through the SQL Client Configuration Utility.
  
     1. From the client workstation, start SQL Client Configuration Utility.
  
     2. Click the Advanced tab or click the Advanced button.
  
     3. Add the following values in the Advanced window:
  
           Server:            <server name>
           DLL name:          DBNMPNTW
           connection string: \\<server name>\pipe\sql1\query
  
     In case of 16-bit tools, the DLL name should be DBNMP3.
  
     4. Click the Add\Modify button and then click Done to exit from the SQL
        Server Client Configuration Utility.
  
  

• Modify the application to trap the connection busy or pipe busy errors and force the application to retry connecting to the server.
• Reconfigure the clients to use the sockets Net-Library, which avoids busy signals (due to the TCP/IP backlog queue feature).