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

*/