INF: How to Troubleshoot Orphaned Connections in SQL Server

• Microsoft SQL Server versions 4.2x, 6.0, 6.5, 7.0

SUMMARY

When a SQL Server client disconnects from a SQL Server, the connection process should be cleared up on the server side. If the connection processes are not cleared up for any reason, they become "orphaned" or "ghost" processes, which may use up valuable resources such as locks and user connections. The orphaned processes are typically caused by improper closing of client applications and network-related problems, and the remedies usually require troubleshooting client applications and fine- tuning network configurations.

MORE INFORMATION

When you troubleshoot this problem, keep the following in mind:

• SQL Server as an application does not and should not proactively probe the client connection to determine its current status. The lower level Inter-Process Communications (IPCs), such as named pipes, IPX/SPX or TCP/IP sockets, are responsible for managing the client connections.



• An IPC typically has its own mechanism to manage the client connections. When client connections become non-responsive for a certain amount of time, typically the Windows NT Server computer will either detect this by sending the "keep alive" probes, or clear up the connection after it is idle for a configured amount of time.



• Under certain situations such as client general protection fault, the client may still respond to server probes even if the application is already dead. In this case, the Windows NT Server computer may keep this client connection indefinitely, as long as the client is not shut down.



• If a Windows NT Server computer does not close a dead connection for any reason, SQL Server rightfully assumes this connection is still active, and therefore does not clear it up.



• If the Windows NT Server computer has successfully closed the connection, but the client process still exists on the SQL Server as indicated by sp_who, then it may indicate a problem with SQL Server's connection management. In this case, you should work with your primary support provider to resolve this issue.

1. Identify the orphaned processes using sp_who, which may tell you which applications were associated with these processes through the host names.



2. After you identify these orphaned processes, you may choose to either ignore them if they are not holding any locks or using many connections, or kill them using the SQL Server KILL command.



3. Check with the application users for any improper procedures of closing applications, such as warm or cold restart of workstations without exiting the applications first. Check whether there is any history of the workstation becoming unstable, such as a general protection fault, and so forth. Correct those improper procedures or stability problems if they do exist.



4. Check whether the IPC session is still active on the Windows NT Server computer where SQL Server is running. Depending on the IPCs you are using, the commands are different. For example, if you are using named pipes, the command is "NET SESSION" or "NET FILES"; if it is a TCP/IP sockets connection, you can use "NETSTAT" to display active TCP sessions; in case of IPX/SPX, you may have to use the Performance Monitor to monitor the "Connections Open" for "NWLink SPX."



5. If the IPC sessions are still active on the Windows NT Server computer, it is perfectly normal for SQL Server to keep those connection processes. When Windows NT clears up the IPC sessions, SQL Server will be notified and clear up the connection processes accordingly. You may be able to adjust certain Windows NT network parameters to shorten the time period that Windows NT has to wait before clearing up the dead sessions.
   
   Again, depending on the IPCs you are using, the parameters involved are very different. For information on how to modify these registry settings to match those mentioned below, see the following articles in the Microsoft Knowledge Base:
   

   Q120642 : TCP/IP & NBT Configuration Parameters for Windows NT
   
   Q120642 : Tuning NWLINK Registry Parameters

   
   
   • Named Pipes: Named Pipes are implemented in Server Message Block (SMB) layer on top of other transport protocols such as TCP/IP, NetBEUI, or NWLink IPX/SPX. A thin layer called NetBIOS is typically implemented between the SMB and the transport layer. Therefore, a convenient way of adjusting how long a non-reponsive Named Pipes session has to wait before being closed is through adjusting the KeepAlive parameters of the relevant NetBIOS layer. For TCP/IP, the NetBIOS layer involved is NBT (NetBIOS over TCP), and the parameter involved is SessionKeepAlive in the following registry key:
     

     
              KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Netbt\Parameters
           

     If the protocol used is NWlink IPX/SPX, the involved parameters are KeepAliveCount and KeepAliveTimeout in the following registry key:
     

     
              HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NWNBLink\Parameters
           

     Please note that some Named Pipes implementations over NWLink may bypass the NetBIOS layer (DirectHosting), which means the above NWNBlink NetBIOS parameters do not apply.
   
   
   
   • TCP/IP Sockets: Windows NT Server will periodically send "keep alive packets to clients, and clients are expected to respond. If for any reason the client fails to respond to those packets, Windows NT clears up the TCP session after trying for a configured number of times. The parameters involved are KeepAliveInterval, KeepAliveTime, and TcpMaxDataRetransmissions in the following registry key:
     

     
              HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
           

   
   
   
   • IPX/SPX: Similar to TCP/IP, Windows NT Server periodically sends "keep alive" packets and clients respond. Windows NT clears up the SPX connections if it fails to receive any responses after sending a configured number of "keep alive" packets. The parameters involved are KeepAliveCount and KeepAliveTimeout in the following registry key:
     

     
              HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NWLnkSPX\Parametes
           

   
   
   
   Please note that if the "keep alive" parameters for your IPCs are configured to never timeout, Windows NT will keep the IPC sessions indefinitely, even if the clients are completely shut down. In this case, SQL Server will keep these client processes indefinitely as well, which is expected.
   
   For further information about the parameters, please consult your Windows NT documentation or Windows NT Resource Kit. If you suspect your Windows NT Server computer does not clear up those sessions according to the configuration parameters, you can contact your primary Windows NT support provider for help.



6. If the IPC session no longer exists on the Windows NT Server computer, but SQL Server still keeps client process as shown by sp_who, you can use the KILL command to clear up the process as a temporary solution, and contact your primary SQL Server support provider for further assistance.

Additional query words: sql6 connectivity gp gpf hang reboot time out

Keywords : kbusage SSrvGPF SSrvLAN SSrvLock
Version : WINNT:4.21,6.0,6.5,7.0
Platform : WINDOWS
Issue type : kbhowto kbinfo