Thursday, 5 January 2012

How to kill all sql processes in database

Declare @spid varchar(max)
set @spid=Null
SELECT  @spid = COALESCE( @spid +'; Kill ' +  ' ','') + CAST(spid AS VARCHAR(10))
  FROM master..sysprocesses  WHERE dbid = DB_ID('db name')
   AND spid != @@SPID
if @spid is not null
set @spid='Kill '+ @spid
print @spid
exec(@spid)
select *  FROM master..sysprocesses 
WHERE dbid = DB_ID('db name')
 AND spid != @@SPID

No comments:

Post a Comment