Orphaned or Ghost processes

 

 

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

 

Testing Zone

---------------------

EXEC up_KILL_SLEEPINGSESSION

@p_DBNAME='MYDB'

 

*/

IF EXISTS (SELECT *

           FROM   sys.sysobjects

           WHERE  type = 'P'

                  AND name = 'up_KILL_SLEEPINGSESSION')

      BEGIN

            DROP  Procedure  up_KILL_SLEEPINGSESSION

      END

 

GO

 

CREATE Procedure [dbo].[up_KILL_SLEEPINGSESSION]

      (

            @p_DBNAME VARCHAR(MAX)

    )

AS

    DECLARE @k_spId       VARCHAR(MAX)

    DECLARE @k_SQLString  VARCHAR(MAX)

      DECLARE @k_Flag       DECIMAL

 

      BEGIN

        SET @k_Flag=0

      

        DECLARE db_Orfen CURSOR FOR

                SELECT SPID

                FROM MASTER..SYSPROCESSES

                WHERE DBID = DB_ID(@p_DBNAME)

                      AND SPID != @@SPID

                      AND status= 'sleeping'

    

        OPEN db_Orfen

        FETCH NEXT FROM db_Orfen

                   INTO @k_spId

 

        WHILE @@FETCH_STATUS = 0  

                    BEGIN

                    SET @k_SQLString= 'KILL '+@k_spId

                    EXEC (@k_SQLString)

                   

                    SET @k_Flag=1

                    FETCH NEXT FROM db_Orfen

                               INTO @k_spId

              END

                      

       CLOSE db_Orfen  

         DEALLOCATE db_Orfen 

        

         IF @k_Flag=1

            BEGIN

                SELECT 'orphaned processes KILLED' AS Status

            END

         ELSE

            BEGIN

                SELECT 'ALLERT -- Sorry' AS Status

            END  

      END

GO

 

 

 

Posted By: MR. JOYDEEP DAS