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

        描述:该代码终止数据库的所有用户进程. 用户进程是 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