A client may have its connection abruptly severed from the server, and as a result, the client process may be 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.
The following questions and answers provide frequently requested information on orphaned sessions.
Q: What is an orphaned session?
A: An orphaned session is a session that remains open on the server side after the client has disconnected.
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 SQL Server Books Online.
Q: When do orphaned sessions occur?
A: Orphaned sessions occur when the client is unable to free network connections it is holding when it terminates.
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.
Orphaned sessions often are caused by a client computer losing power unexpectedly or being shut down improperly. 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.
Q: What problems can orphaned sessions cause?
A: Open sessions take up one of the SQL Server network connections. The maximum number of connections is limited by the number of server CALs; therefore, orphaned sessions may prevent other clients from connecting.
Typically, a more important issue is that open sessions use server resources, and may have open cursors, temporary tables, or locks. These locks can block other connections from performing useful work, and can sometimes be the result of a major backlog of locks. In severe cases, it can appear that SQL Server has stopped working.
Q: How can I tell if an orphaned session exists and what problems it might be causing?
A: sysprocesses (or stored procedures, such as sp_who) reports information on existing server sessions. Possible orphaned sessions can be identified if the status of a process is AWAITING COMMAND and the interval of time, found by subtracting last_batch from GETDATE(), is longer than usual for the process. If the session hostname is known to be down, it is orphaned.
Q: How do I resolve orphaned sessions?
Windows NT checks inactive sessions periodically to ensure they are active. If a session does not respond, it is closed and SQL Server is notified. The frequency of the checking depends on the network protocol and registry settings. However, by default, Windows NT performs a check only every one or two hours, depending on the protocol used. These configuration settings can be changed in the registry.
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 at which clients are checked to verify they are active.
Warning Do not change these settings on computers running Microsoft Windows 95 or Windows 98 operating systems.
Consider the effect changing these settings may have on other applications on your system, in particular, applications with Internet connectivity, such as Microsoft Internet Information Services (IIS) or Microsoft Internet Explorer. In addition, consider the effects of using connections that are charged on a per-packet basis.
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.
Note Do not increase the ping frequency to less than 1 minute, as network I/O and CPU usage for pings may become excessive.