Kill all Orphan Processes on a SQL Server

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

*/