Orphaned Sessions
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™ 2000 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.
What is an orphaned session?
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 Troubleshooting Orphaned Users.
When do orphaned sessions occur?
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 terminated (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 shutdown. Orphaned sessions can also occur due to a hung application that never completely terminates, resulting in a dead connection. Windows NT does not know that the connection is dead and continues 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.
What problems can orphaned sessions cause?
Open sessions take up one of the SQL Server network connections. The maximum number of connections is limited by the number of server Client Access Licenses (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 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.
How can I tell if an orphaned session exists and what problems it might be causing?
The sysprocesses table (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 host name is known to be down, it is orphaned.
How do I resolve orphaned sessions?
Windows NT periodically checks inactive sessions 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 only performs a check 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 statement. 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 with which clients are checked to verify they are active. Changing these settings affects other application connections. The following points should be considered before making any changes.
Warning Do not change these settings on computers running Microsoft Windows® 95 or Microsoft Windows 98.
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 affects 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.
KeepAliveTime for TCP/IP
| Key: | Tcpip\Parameters |
| 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. |
SessionKeepAlive for Named Pipes
| Key: | Netbt\Parameters |
| 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. |
Do not increase the ping frequency to less than 1 minute, as network I/O and CPU usage for pings may become excessive.