Amir Mirkamali

امیر میرکمالی

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
Comments are closed