用T-SQL强制所有用户退出当前数据库-PLSQL

2008-07-23 作者:bitsCN整理来源:中国网管联盟点评 投稿 收藏

目的:强制所有用户退出当前服务器。网管联盟bitsCN@com

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

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

网管朋友网www_bitscn_net

结果集: NA

网管u家u.bitsCN.com

结果集排序: NA 网管朋友网www_bitscn_net

使用的 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

网管u家www.bitscn.net

        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 中国网管联盟bitsCN.com
        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)) 中国网管联盟bitsCN.com
        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) 网管网www.bitscn.com
        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 网管朋友网www_bitscn_net
        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

网管u家u.bitsCN.com

        WHERE SPID <= 50 OR SPID = @@SPID
        END
        GO 网管联盟bitsCN@com

网管网www_bitscn_com

顶一下

TAGs：当前 数据库 退出用户强制所有 NVARCHAR 进程

上一篇：关于MySQL编码问题的经验总结-PLSQL 下一篇：SQL - 使用 Union 的注意事项-PLSQL

用T-SQL强制所有用户退出当前数据库-PLSQL

2008-07-23 作者:bitsCN整理 来源:中国网管联盟 点评 投稿 收藏

2008-07-23 作者:bitsCN整理来源:中国网管联盟点评投稿收藏