目的:強制所有用戶退出當前服務器。

描述:該代碼終止數據庫的所有用戶進程. 用戶進程是 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