Thursday 17 March 2011

Kill Sleeping Connection in Sql

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

No comments:

Post a Comment