ALTER PROCEDURE [dbo].[SleepedConnKill] --15-Oct-2010 -- give name SleepedConnKill
AS
SET NOCOUNT ON;
Declare @StrKill Varchar(30)
Declare @SPID_Cur Numeric(18,0)
Declare SysProcess_Cursor Cursor LOCAL For
Select SPID From master..sysprocesses Where SPID>50 And Status='sleeping' And DateDiff(MINUTE ,Last_Batch,GetDate())>5
Open SysProcess_Cursor
Fetch Next From SysProcess_Cursor Into @SPID_Cur
While @@Fetch_Status = 0
Begin
Set @StrKill = 'KILL '+ convert(varchar(10),@SPID_Cur)
EXEC(@StrKill)
Fetch Next From SysProcess_Cursor Into @SPID_Cur
End
Close SysProcess_Cursor
Deallocate SysProcess_Cursor
Return