Create PROCEDURE [dbo].[SleepedConnKill]
AS
SET NOCOUNT ON;
Declare @StrKill Varchar(30)
Declare @SPID_Cur Numeric(18,0)
Declare SysProcess_Cursor Cursor LOCAL For
Select SPID From sys.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
Thursday, 17 March 2011
Kill Sleeping Connection in Sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment