declare @sql varchar(100)while 1=1 beginselect top 1 @sql = 'kill '+cast(spid as varchar(3)) from master..sysprocesses where spid > 50 and spid <> @@spid if @@rowcount = 0 breakexec(@sql) end
或: create proc killspid (@dbname varchar(20)) as begin declare @sql nvarchar(500) declare @spid int set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) open getspid fetch next from getspid into @spid while @@fetch_status < >-1 begin exec('kill '+@spid) fetch next from getspid into @spid end close getspid deallocate getspid end --用法 use master exec killspid '数据库名'
你可以结合SP_LOCK和SQL TRACER进行检查。
beginselect top 1 @sql = 'kill '+cast(spid as varchar(3))
from master..sysprocesses
where spid > 50 and spid <> @@spid if @@rowcount = 0
breakexec(@sql)
end
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status < >-1
begin
exec('kill '+@spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end --用法
use master
exec killspid '数据库名'
那就必须用KILL 杀掉对应的SPID