拾遗笔记

kill所有连接到某库的连接

在进行sqlserver数据库恢复时,需要关闭所有连接到此数据库的连接。
下面这个存储过程,可以做到,

use   master
go

create   proc   p_killspid
@dbname   sysname --要关闭进程的数据库名
as    
declare   @s   nvarchar(1000)
declare   tb   cursor   local   for
select   s= 'kill   '+cast(spid   as   varchar)
from   master..sysprocesses  
where   dbid=db_id(@dbname)

open   tb  
fetch   next   from   tb   into   @s
while   @@fetch_status=0
begin
exec(@s)
fetch   next   from   tb   into   @s
end
close   tb
deallocate   tb
go
--用法    
exec   p_killspid     'dbname'

Comments

comments powered by Disqus