b<>【赛迪网－it技术报道】目的:强制所有用户退出当前服务器。

描述:该代码终止数据库的所有用户进程. 用户进程是 sp_who, 或者 sp_who2, spid > 50 返回的所有进程. 代码使用 sp_who (不牵扯任何系统表)查找运行在当前数据库上的进程, 并通过使用 kill <进程号> 终止进程..

用户可以指定 @maxattemptstokilleachuserprocess 变量(尝试终止单个进程的次数, 默认值 3). 如果超过最大值, 代码将返回一个错误 (例如, 进程不能被终止).

结果集: na

结果集排序: na

使用的 tables/views: na

revisions

date developer description of revision version

========= =============== ================================= ===========

05/05/2005 omri bahat initial release 1.00

==================================================================================

copyright ?sql farms solutions, www.sqlfarms.com. all rights reserved.

this code may be used at no charge as long as this copyright notice is not removed.

==================================================================================*/

declare @maxattemptstokilleachuserprocess int

declare @currentattempts int

declare @servername nvarchar(128)

declare @databasename nvarchar(128)

declare @sqlcommand nvarchar(128)

declare @spid int

declare @loginname nvarchar(128)

set nocount on

set @maxattemptstokilleachuserprocess = 3

-- 得到服务器和数据库名称

set @servername = cast(isnull(serverproperty('servername'), 'unknown') as sysname)

-- 该表记录用户进程标识.

if object_id('tempdb..#tbluserprocesses', 'u') is not null

drop table #tbluserprocesses

create table #tbluserprocesses (

spid int,

ecid int,

status nvarchar(256),

loginname nvarchar(128),

hostname nvarchar(128),

blockedby nvarchar(128),

databasename nvarchar(128),

command nvarchar(256))

insert into #tbluserprocesses

exec sp_who

delete from #tbluserprocesses

where spid <= 50 or spid = @@spid

while exists(select * from #tbluserprocesses with (nolock))

begin

set @sqlcommand = null

set @spid = null

set @loginname = null

set @databasename = null

select top 1 @sqlcommand = 'kill ' + cast(spid as nvarchar(32)),

@spid = spid,

@loginname = isnull(loginname, 'na'),

@databasename = databasename

from #tbluserprocesses with (nolock)

set @currentattempts = 0

while @currentattempts <= @maxattemptstokilleachuserprocess

begin

exec(@sqlcommand)

if @@error <> 0

begin

print(n'error killing process ' + cast(@spid as varchar(32)) + n', of login ' + @loginname

+ n', on database ' + @databasename

+ n'. the process was probably terminated by the user.')

break

end

-- 清除必要的表

waitfor delay '00:00:03'

-- 确认进程真正终止

insert into #tbluserprocesses

exec sp_who @spid

if @@rowcount = 0

break

else

set @currentattempts = @currentattempts + 1

end

if @currentattempts > @maxattemptstokilleachuserprocess

begin

print(n'the number of attempts to kill process ' + cast(@spid as varchar(32)) + n', of login ' + @loginname

+ n', on database ' + @databasename + n' exceeded the maximum number of retry attempts. script is aborting.')

return

end

delete from #tbluserprocesses

insert into #tbluserprocesses

exec sp_who

delete from #tbluserprocesses

where spid <= 50 or spid = @@spid

end

go