Kill the Process!

create procedure sp_proc_kill @DatabaseName nvarchar(50)
as
declare @Spid varchar(20);
declare @Command varchar(50);
--set @DatabaseName = 'MyDatabase';
print 'This query''s SPID: ' + convert(varchar, @@spid);
-- Select all SPIDs except the SPID for this connection
declare SpidCursor cursor for
select spid from master.dbo.sysprocesses
where dbid = db_id(@DatabaseName)
and spid != @@spid
open SpidCursor
fetch next from SpidCursor into @spid
while @@fetch_status = 0
begin
print 'Killing process: ' + rtrim(@spid);
set @Command = 'kill ' + rtrim(@spid) + ';';
print @Command;
execute(@Command);
fetch next from SpidCursor into @spid
end
close SpidCursor
deallocate SpidCursor

Orginally posted by Andy @ http://acdub.com/blog/2011/04/27/sql-server-kill-all-processes-connected-to-a-database/

Leave a Reply

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