|
源代码网推荐
目的:强制所有用户退出当前服务器。
描述:该代码终止数据库的所有用户进程. 用户进程是 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
源代码网供稿. |
