Monday, 8 June 2009

Script to kill all processes for a database except your own

Declare @sql varchar(100)
Declare @dbname varchar(100)
Declare @spid int
set @dbname = 'PeninsulaukSP1'
DECLARE CursorQuery CURSOR FOR
SELECT spid FROM master..sysprocesses WHERE dbid = DB_ID(@dbname) AND spid != @@SPID
OPEN CursorQuery

FETCH NEXT FROM CursorQuery
INTO @spid
PRINT 'Starting Kill Process'
WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = RTRIM(convert(varchar(3),@spid))
PRINT 'Killing Process ' + @sql
EXEC('KILL '+ @sql)
FETCH NEXT FROM CursorQuery
INTO @SPID
END
CLOSE CursorQuery
DEALLOCATE CursorQuery