SQL Server：使用存储过程终止进程

Question

我想修改以下内容，因为它似乎不会杀死进程 - 我认为它应该断开用户连接（这是一样的吗？）。我希望能够终止特定数据库的所有进程 - 如何修改以下内容：

create procedure [dbo].[sp_killusers](@database varchar(30))
as
----------------------------------------------------
-- * Created By David Wiseman, Updated 19/11/2006
-- * http://www.wisesoft.co.uk
-- * This procedure takes the name of a database as input
-- * and uses the kill statment to disconnect them from
-- * the database.
-- * PLEASE USE WITH CAUTION!!
-- * Usage:
-- * exec sp_killusers 'databasename'
----------------------------------------------------
set nocount on
declare @spid int
declare @killstatement nvarchar(10)

-- Declare a cursor to select the users connected to the specified database
declare c1 cursor for select request_session_id
                                from sys.dm_tran_locks
                                    where resource_type='DATABASE'
                                    AND DB_NAME(resource_database_id) = @database
open c1
fetch next from c1 into @spid
-- for each spid...
while @@FETCH_STATUS = 0
begin
      -- Don't kill the connection of the user executing this statement
      IF @@SPID <> @spid
      begin
            -- Construct dynamic sql to kill spid
            set @killstatement = 'KILL ' + cast(@spid as varchar(3))
            exec sp_executesql @killstatement
            -- Print killed spid
            print @spid
      end
      fetch next from c1 into @spid
end
-- Clean up
close c1
deallocate c1

更新

上面不起作用，即它不会终止进程。

它不会终止进程。我看着 活动监视器及其静态 显示该过程仍在继续，我可以 看到我的查询仍在工作 查询窗口。当我执行“kill 53”时， 查询在查询窗口中停止，并且 该进程已从活动中消失 监视器！所以 thkill 有效，但这个程序不起作用，为什么？

Answer 1

您是否只是想停止特定数据库上的所有活动，以便对其进行一些维护？

如果是这样，您可以执行以下操作：

ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

这将终止访问该数据库的所有其他 SPID，并将该数据库置于单用户模式。然后执行维护操作，然后执行以下操作：

ALTER DATABASE myDB SET MULTI_USER;

Answer 2

我对这个剧本很熟悉。它会杀死所有正在使用数据库的 SPID，是的。您需要在正确的权限下运行它 - 并非任何用户都可以杀死 SPID。

此外，您的应用程序可能会尝试维护与数据库的持久连接，因此可能会在您终止其 SPID 后不久重新连接。

Answer 3

您可能想尝试使用 exec 而不是 sp_exec （并不是说它应该有任何区别）

SET @killstatement = 'KILL ' + cast(@spid as varchar(3)) 
EXEC (@killstatement)

Answer 4

您是否尝试过对程序运行时实际发生的情况进行任何调试/输出？例如，您可以将 @killstatement 修改为 nvarchar(max) 并包含一些详细输出（例如以下内容）并发布结果吗？基本上将开始/结束块中的所有内容替换为以下内容：

-- Construct dynamic sql to kill spid
select  @killstatement = N'
            select  *
            from    sys.dm_exec_sessions s
            join    sys.dm_exec_connections c
            on      s.session_id = c.session_id
            where   c.session_id = @spid;

            kill ' + cast(@spid as varchar(3)) + ';

            select  *
            from    sys.dm_exec_sessions s
            join    sys.dm_exec_connections c
            on      s.session_id = c.session_id
            where   c.session_id = @spid;           
        ';
-- Print & Exec
print @killstatement;
exec sp_executesql @killstatement, N'@spid smallint', @spid;
print @spid;

与在连接中显式执行相比，过程代码中的任何内容没有理由表现得不同 - 假设您具有适当的权限，正在杀死有效的 spid 等。如果您可以发布像上面这样的一些调试结果（以及您可能尝试过的其他任何内容），这将有助于找出问题所在。您可能还想包含您正在使用的游标声明结果的调试输出，以确保您实际上获得了您想要终止的会话 - 即只需包含您在游标声明中使用的相同选择即可输出结果集，像这样：

declare c1 cursor for select request_session_id
                                from sys.dm_tran_locks
                                    where resource_type='DATABASE'
                                    AND DB_NAME(resource_database_id) = @database

-- Debug output - sessions we should try and kill...
select  request_session_id
from    sys.dm_tran_locks
where   resource_type='DATABASE'
AND     DB_NAME(resource_database_id) = @database;

如果您可以发布结果，希望这会给我们一些继续进行的东西。

Answer 5

很可能这些都不适用于您，但以防万一，这里有一些我几年前在处理此类内容时遇到的奇怪情况（所有 SQL 2005）。

• 你不能终止你自己的联系。
• 在我使用的代码中，我确保永远不会尝试杀死 51 以下的任何 spid。（这些是系统连接；我不知道它们是否可以被杀死，但我不会尝试） .)
• 如果连接正在处理事务，则必须先回滚该事务，然后才能终止该事务。大量事务可能需要大量时间才能回滚。
• 当心连接池。他们就像不死生物一样——杀死他们，他们就会立即回来，通常在一秒钟之内。

在运行此过程时运行 SQL 事件探查器并跟踪登录和注销可能会有所启发，特别是对于连接池问题。

Answer 6

这在 SQLServer 2000 中对我有用

DECLARE @DbName VARCHAR(100)
DECLARE @SPID INT
DECLARE @TranUOW UNIQUEIDENTIFIER
DECLARE @KillStmt NVARCHAR(100)

SET @DbName = 'MyDatabase'

-----------------------------------
-- Kill distributed transactions

DECLARE dist CURSOR FOR
    SELECT DISTINCT req_transactionUOW
        FROM master..syslockinfo
        WHERE db_name(rsc_dbid) = @DbName
              AND req_transactionUOW <> '00000000-0000-0000-0000-000000000000'

OPEN dist

FETCH NEXT FROM dist INTO @TranUOW

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @KillStmt = 'kill ''' + CAST(@TranUOW AS VARCHAR(50)) + ''''

    PRINT @KillStmt
    EXECUTE(@KillStmt)

    FETCH NEXT FROM dist INTO @TranUOW
END

CLOSE dist
DEALLOCATE dist

-----------------------------------
-- Kill user connections

DECLARE cur CURSOR FOR
    SELECT spid
        FROM master..sysprocesses
        WHERE db_name(dbid) = @DbName
              AND spid > 50

OPEN cur

FETCH NEXT FROM cur INTO @SPID

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @KillStmt = 'kill ' + CAST(@SPID AS VARCHAR(10))

    PRINT @KillStmt
    EXECUTE(@KillStmt)

    FETCH NEXT FROM cur INTO @SPID
END

CLOSE cur
DEALLOCATE cur