Performance - Kill all Orphan Processes on a SQL Server

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        22 Aug 2012

Description

This is a variation on the usp_KillProcesses stored procedure.  
The kill criteria now is all user processes on the SQL Server whose last_batch time is longer ago than the supplied number of hours (default 24 hours).

This is extremely useful for cleaning up after messy 3rd party applications and I have used it very effectively to manage connection levels on many production servers in the past.  Not ideal, but a necessary evil when you don't have access to tighten up the connection handling code.  Usually, it gets scheduled in the quiet hours and runs sub-second even for several thousand connections.


Code

Stored Procedure:


use master
go

DROP PROCEDURE dbo.usp_KillOrphanProcesses
GO

CREATE PROCEDURE dbo.usp_KillOrphanProcesses @ProcessesDormantLongerThan INT = 24


AS
/***************************************************************
Purpose:   To kill all processes in a specific database that have been dormant
           longer than the specified number of hours (defaults to 24)
          
           This has been very useful over the years in helping to manage connection resources
           of some very poorly written/ported applications (in-house and 3rd party)    

          
           Parameters are as follows:
                   @ProcessesDormantLongerThan             - Number of hours since the last activity on the connection
                                                             Defaults to 24
          
Author:        ChillyDBA
History:   22 Aug 2012

****************************************************************/

SET NOCOUNT ON

DECLARE
    
@spid SMALLINT,
    
@spidStr VARCHAR(10)


DECLARE spid_curs CURSOR
    FOR SELECT
            
spid
        
FROM master..sysprocesses
        
WHERE last_batch < DATEADD(hh, @ProcessesDormantLongerThan * -1, GETDATE())
        AND
spid > 50
          
    
FOR READ ONLY

OPEN
spid_curs
FETCH NEXT FROM spid_curs INTO @spid

SELECT
    
'Killed Process:'

WHILE (@@fetch_status = 0)
    AND (
@@error = 0)
    
BEGIN
        SELECT
            
@spidStr = CONVERT(VARCHAR(10), @spid)
        
EXEC ('kill ' + @spidStr
            
)

        
SELECT
            
@spid

        
FETCH NEXT FROM spid_curs INTO @spid
    
END

CLOSE
spid_curs
DEALLOCATE spid_curs


SET NOCOUNT OFF
RETURN

/*
   EXEC master.dbo.usp_KillOrphanProcesses NULL
   EXEC master.dbo.usp_KillOrphanProcesses 0  

*/

ċ
usp_KillOrphanProcesses.sql
(2k)
Andy Hughes,
Aug 23, 2012, 10:41 AM
Comments