kill all user in a database, very useful

weixin_30311605

于 2009-12-08 17:28:00 发布

阅读量59

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/beyondjay/archive/2009/12/08/1619609.html

本文提供了一种实用的方法来处理被锁定的数据库问题，通过获取当前连接到特定数据库的所有用户会话，并逐一终止这些会话来准备数据库恢复。该过程涉及创建临时表存储活动会话、循环遍历并使用 KILL 命令断开会话。

It's very useful to tackle a locked database, when you decide to restore it.

Declare   @tblConnectedUsers   Table   (
SPID int )

Declare   @vcSQLText varchar(200),
@iSPID int

--Get   the   currently   connected   users
Insert   into     @tblConnectedUsers
Select   p.spid
from   master.dbo.sysprocesses   p   (nolock)
join   master..sysdatabases   d   (nolock)   on   p.dbid   =   d.dbid
Where   d.[name]   =   'dbname'   -->   database   name   here

--Loop   though   the   connected   users   and   kill   their   connections
While   1   =   1
Begin

Select   top   1   @iSPID   =   SPID
From     @tblConnectedUsers
Where   SPID   >   IsNull(@iSPID,   0)
order   by   SPID   asc

--   break   when   there   are   no   more   SPIDs
If   @@RowCount   =   0
Break

--Build   the   SQL   string
Set   @vcSQLText   =   'Kill   '   +   Convert(varchar(10),   @iSPID)

Exec(   @vcSQLText   )

End