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
（責任編輯：盧兆林）
