When orphaned processes occur, here's how to troubleshoot the problem.
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, they become orphaned or ghost processes that use up valuable resources like 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:
The lower-level IPCs such as named pipes, SPX/IPX, or TCP/IP sockets are responsible for managing the client connections.
When client connections are nonresponsive for a certain amount of time, the Windows NT Server typically will either detect this by sending the "keep alive" probes or clear the connection after it idles for a configured amount of time.
In this case, the Windows NT Server may keep this client connection indefinitely, as long as the client is not shut down.
Contact 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.
This will indicate the applications that were associated with these processes.
After you identify the orphaned processes, you can choose to either ignore them (if they are not holding any locks or using many connections) or kill them using the SQL Server KILL statement.
Check with application users to ascertain if they have closed applications incorrectly; correct any improper procedures.
An example of incorrectly closing an application is doing a warm or cold reboot of a workstation without first exiting all applications.
Check the history of the workstation and correct stability problems.
Some examples of an unstable workstation are hangs and GP-faults.
Check whether the IPC session is still active on the Windows NT Server where SQL Server is running.
Depending on which IPC you use, the commands for using it are different. For example, if you are using named pipes, the command is NET SESSION or NET FILES. If you are using TCP/IP sockets, you can use NETSTAT to display active TCP sessions. If you are using SPX/IPX, you may have to use Performance Monitor to monitor the open connections for NWLink SPX.
HKEY_LOCAL_MACHINE
\SYSTEM
\CurrentControlSet
\Services
\LanmanServer
\Parameters
HKEY_LOCAL_MACHINE
\SYSTEM
\CurrentControlSet
\Services
\Tcpip
\Parameters
HKEY_LOCAL_MACHINE
\SYSTEM
\CurrentControlSet
\Services
\NWLnkSPX
\Parameters
Note If the "keep alive" parameters for your IPCs are configured to never time out, Windows NT keeps the IPC sessions indefinitely, even if the clients are completely shut down. In this case, SQL Server also keeps these client processes indefinitely.
For more information about the parameters, see your Windows NT documentation or your Windows NT Resource Kit. If you suspect your Windows NT Server does not clear those sessions according to your configuration parameters, contact your primary support provider.
If the IPC session no longer exists on the Windows NT Server but SQL Server still keeps client processes (shown by sp_who), you can use the KILL statement to clear the process as a temporary solution, and then contact your primary support provider for further assistance.