KILL ALL INACTIVE SPIDS

Please use this only if you know what you are doing. I have intentionally commented the kill command. If some one knows how to uncomment it, only then, this script will work.

—————————-
—————————-
First run this to find the offending database…..
—————————-
—————————-

SELECT DB_NAME(dbid) as ‘Test’,
COUNT(dbid) as ‘Total Connections’
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
SELECT @@MAX_CONNECTIONS AS ‘Max Allowed’

——————————
——————————
Then run this to kill the connections to the desired DB
—————————–
—————————–
USE master
go

DECLARE @dbname sysname

SET @dbname = ‘Events’

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE (‘KILL ‘ + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END

—————————————————————-
—————————————————————-
KILL ALL SLEEPING CONNECTIONS
—————————————————————–
—————————————————————–

DECLARE

@v_spid INT

DECLARE

Users CURSOR

FAST_FORWARD FOR

SELECT SPID

FROM master.dbo.sysprocesses (NOLOCK)

WHERE spid>50

AND status=’sleeping’

AND DATEDIFF(mi,last_batch,GETDATE())>=60 –Check sleeping connections that exists before 60 min..

AND spid<>@@spid

OPEN

Users

FETCH

NEXT FROM Users INTO @v_spid

WHILE

(@@FETCH_STATUS=0)

BEGIN

PRINT ‘KILLing ‘+CONVERT(VARCHAR,@v_spid)+’…’

–EXEC(‘KILL ‘[email protected]_spid)

FETCH NEXT FROM Users INTO @v_spid

END

CLOSE

Users

DEALLOCATE

Users

About: Niraj Kumar


Leave a Reply

Your email address will not be published. Required fields are marked *