When a Microsoft 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 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:
1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
Here is a simple script to KILL all the "orphaned" or "ghost" processes.
Performance Tuner of DB
Kill the "orphaned" or "ghost" processes
IF EXISTS (SELECT *
WHERE type = 'P'
AND name = 'up_KILL_SLEEPINGSESSION')
DROP Procedure up_KILL_SLEEPINGSESSION
CREATE Procedure [dbo].[up_KILL_SLEEPINGSESSION]
DECLARE @k_spId VARCHAR(MAX)
DECLARE @k_SQLString VARCHAR(MAX)
DECLARE @k_Flag DECIMAL
DECLARE db_Orfen CURSOR FOR
WHERE DBID = DB_ID(@p_DBNAME)
AND SPID != @@SPID
AND status= 'sleeping'
FETCH NEXT FROM db_Orfen
WHILE @@FETCH_STATUS = 0
SET @k_SQLString= 'KILL '+@k_spId
FETCH NEXT FROM db_Orfen
SELECT 'orphaned processes KILLED' AS Status
SELECT 'ALLERT -- Sorry' AS Status
Posted By: MR. JOYDEEP DAS