Microsoft KB Archive/176257

= PRB: Client Login Does Not Time Out If the Server Is Offline =

Article ID: 176257

Article Last Modified on 11/14/2003

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q176257



SYMPTOMS
A SQL Server client application may not correctly time out according to the set timeout value in the application if the server it tries to connect to is shut down or otherwise offline. For example, if the timeout value is set to 10 seconds, the application may not time out until 90 seconds later. This can happen under either Named Pipes or TCP/IP sockets Net-Libraries, although the delay may be shorter under TCP/IP sockets.



CAUSE
By default, when a SQL Server client application tries to connect to SQL Server through TCP/IP sockets or Named Pipes with TCP/IP as the transport, the TCP transport layer must resolve the server name to its IP address through WINS. Even if a server has been offline for a while, the server name and IP address can still exist in the WINS database, due to the long refresh rate in WINS. As a result, the corresponding IP address is returned to the client successfully. The next step is to resolve the IP address to the machine address of the server through Address Resolution Protocol (ARP) broadcast packets. Because the server computer is really offline now, such ARP broadcasts eventually fail after a few retries. By default, these retries cause a delay of about 45 to 50 seconds. In the case of Named Pipes, the redirector retries the entire address resolution sequence, starting with querying the WINS database, one more time. As a result, the total delay is doubled to 90 to 100 seconds. For socket connections, the delay is 45 to 50 seconds.

Thus, even if the application login timeout is set to a short period of time, say 30 seconds, it does not time out until the underlying IPC returns the control back to the Net-Library. In this situation, the IPC does not return the control back to Net-Library until 45 seconds (for TCP/IP sockets) or 90 seconds (for named pipes) later. Therefore, the application timeout can be delayed for up to 45 or 90 seconds.



WORKAROUND
The only adjustable parameter affecting this behavior is TcpMaxConnectRetransmissions, which controls how many times ARP packets are retransmitted. The default is three retries, for a total of four transmissions. Because each retransmission timeout is doubled with each successive retransmission in a given connection, reducing the number of retries by one can cut the total timeout length by half. For information on how to change this parameter, please refer to the following article in the Microsoft Knowledge Base:

120642 : TCP/IP and NBT Configuration Parameters for Windows 2000 or Windows NT



MORE INFORMATION
This timeout behavior can occur with both ODBC and DB-Library applications, and it is totally independent of the application or the application programming interfaces (APIs) used. This delay is directly caused by the behaviors of the underlying transport protocols and IPCs used for the client connections. The same IPC, like Named Pipes, can exhibit different behaviors with different transport protocols. For example, Named Pipes over NetBEUI may not have this delay problem because TCP/IP, which is responsible for most of the delay, is not used.

Additional query words: dblib netlib MAC

Keywords: kbnetwork kbprb KB176257

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.