A client may have its connection abruptly severed from the server such that the client process is unable to tell the network to close the connection properly. This may occur for many reasons, including power failures on the client. Microsoft® SQL Server™ does not proactively probe the status of a client connection. Instead, it relies on Microsoft Windows NT® to notify it when a connection needs to be terminated or closed. Windows NT monitors connections and continues to report them as active to SQL Server for the duration of the KeepAliveTime for TCP/IP or SessionKeepAlive for NetBios, which affects Named Pipes clients. SQL Server continues to keep locks owned by the client active until they are killed, or until the connection is terminated or closed by Windows NT.
Do not confuse orphaned sessions with orphaned users. Orphaned users are created when a database is backed up and restored to another system that does not have a corresponding user account configured. For more information about orphaned users, see Troubleshooting Orphaned Users.
If the client terminates cleanly, Windows NT closes the connection and notifies SQL Server. If SQL Server is processing a client command, it will detect the closed connection when it ends the session. Client applications that crash or have their processes killed (for example, from Task Manager), are cleaned up immediately by Windows NT, rarely resulting in an orphaned session.
One common cause of orphaned sessions arises when a client computer loses power unexpectedly, or is powered off without performing a proper shut down. Orphaned sessions can also occur due to a “hung” application that never completely terminates, resulting in a dead connection. Windows NT will not know that the connection is dead and will continue to report the action as active to SQL Server. SQL Server, in turn, keeps the session open and continues to wait for a command from the client.
Typically, a more important issue is that open sessions use server resources, and may have open cursors, temporary tables, or locks. These locks may block other connections from performing useful work, and can sometimes be the result of a major “pile up” of locks. In severe cases, it can appear that SQL Server has stopped working.
To close an orphaned SQL Server session, use the KILL command. All resources held by the session are then released.
If orphaned sessions become a problem, registry settings can be changed on Windows NT to increase the frequency that clients are checked to verify they are active. Changing these settings affects other application connections and the following points should be considered before making any changes.
Warning Do not change these settings on computers running Microsoft Windows® 95/98.
Caution Editing the registry is not recommended; inappropriate or incorrect changes can cause serious configuration problems for your system. Only experienced users should use the Registry Editor. For more information, see your Windows NT documentation.
The registry entries can be altered from HKEY_LOCAL_MACHINE by double-clicking SYSTEM, expanding CurrentControlSet, and then clicking Services.
Value Type: REG_DWORD - Time in milliseconds
Valid Range: 1 - 0xFFFFFFFF
Default: 7,200,000 (two hours)
Description: The parameter controls how often TCP attempts to verify that an idle connection is still intact by sending a keep alive packet. If the remote system is still reachable and functioning, it will acknowledge the keep alive transmission. Keep alive packets are not sent by default; this feature may be enabled on a connection by an application.
Value Type: REG_DWORD - Time in milliseconds
Valid Range: 60,000 - 0xFFFFFFFF
Default: 3,600,000 (1 hour)
Description: This value determines the time interval between keep alive transmissions on a session. Setting the value to 0xFFFFFFF disables keep alives.