Monday, June 11, 2012

tsql program to kill connection sleep more than 10 min can be run in sp agent

exec sp_who
go

DECLARE @v_spid INT
DECLARE c_Users CURSOR
   FAST_FORWARD FOR
   SELECT SPID
   FROM master..sysprocesses (NOLOCK)
   WHERE spid>50
   AND status='sleeping'
   AND DATEDIFF(mi,last_batch,GETDATE())>=10
   AND spid<>@@spid
OPEN c_Users
FETCH NEXT FROM c_Users INTO @v_spid
WHILE (@@FETCH_STATUS=0)
BEGIN
  PRINT 'KILLing '+CONVERT(VARCHAR,@v_spid)+'...'
  EXEC('KILL '+@v_spid)
  FETCH NEXT FROM c_Users INTO @v_spid
END
CLOSE c_Users
DEALLOCATE c_Users


see also


http://support.microsoft.com/kb/137983

1 comment:

  1. It is useful to learn how to set and reset a error.Thank you author for posting this kind of error.

    http://www.wikitechy.com/fix-error/flush-the-print-buffer-in-tsql


    Both are really good.
    Cheers,
    Venkat

    ReplyDelete