SQL Server:使用存储过程终止进程 [英] SQL Server: Kill Process using Stored Procedure

问题描述

我想修改以下内容，因为它似乎不会杀死进程-我认为它应该断开用户连接(这是相同的吗?).我希望能够终止特定数据库的所有进程-如何修改以下内容:

I want to modify the following as it doesn't seem to kill processes - I think its supposed to disconnect users (is this the same?). I want to be able to kill all process for a particular database - how can I modify the below:

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

更新

以上操作无效，即不会终止该进程.

Update

The above doesn't work i.e. it doesn't kill the process.

它不会终止进程.我看着 活动监视器及其静止 显示过程正在继续，我可以 看到我的查询仍然在 查询窗口.当我杀死53"时， 查询在查询窗口中停止，并且 该过程从活动中消失了 监视器！那么杀人有效，但为什么不这样做呢?

It doesn't kill the process. I look at the activity monitor and its still shows the process continuing and I can see my query still working in the query window. When I do "kill 53", the querying stops in the query window and the process is gone from the activity monitor! So th kill works but not this procedure why?

推荐答案

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

I'm familiar with this script. It kills all SPIDs that are using a database, yes. You need to run it under the correct permissions - not just any user can kill SPIDs.

此外，您可能会有一些应用程序尝试维护与数据库的持久连接，因此可能会在杀死它们的SPID之后不久重新连接.

Also, there's a chance you may have applications that try and maintain persistent connections to the DB, and therefore may reconnect shortly after you kill their SPID.

这篇关于SQL Server:使用存储过程终止进程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！