Kill all process related to database with T-SQL

CREATE TABLE #TmpWho
(spid INT, ecid INT, STATUS VARCHAR(150), loginame VARCHAR(150), hostname VARCHAR(150),
blk INT, dbname VARCHAR(150), cmd VARCHAR(150))

INSERT INTO #TmpWho
EXEC       sp_who

DECLARE @spid INT    
DECLARE @getspid CURSOR    

SET @getspid = CURSOR FOR    
      SELECT       spid
      FROM      #TmpWho
      WHERE       dbname = 'YOURDBNAME'

OPEN @getspid    

FETCH NEXT FROM @getspid INTO @spid    

WHILE @@FETCH_STATUS = 0
BEGIN
 KILL @spid --SELECT @spid works fine here
FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid

DROP TABLE #TmpWho

Leave a Reply

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