When a Microsoft SQL Server client disconnects from a SQL Server 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. These processes 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.
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.
However, "keep-alive" packets are not sent by default by an application. The application needs to enable this feature on its connections.
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.
If you suspect orphaned processes exist on your SQL Server, the following
are steps you can take to troubleshoot the problem:
Identify the orphaned processes using sp_who, which may tell you which
applications were associated with these processes through the host
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.
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.
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."
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
Again, depending on the IPCs you are using, the parameters involved are
For more information, click the following article number how to modify these registry settings to match those mentioned below to view the article how to modify these registry settings to match those mentioned below in the Microsoft Knowledge Base:
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-responsive 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:
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:
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
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.
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
For additional information on this subject as it relates to SQL Server 2000, see the topic "Orphaned Sessions" in SQL Server Books Online.