SQL 终止与数据库的所有连接的脚本（超过 RESTRICTED_USER ROLLBACK）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7197574/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
提问by Vaccano
I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build).
我有一个经常从 Visual Studio 数据库项目（通过 TFS 自动构建）重新部署的开发数据库。
Sometimes when I run my build I get this error:
有时，当我运行我的构建时，我会收到此错误：
ALTER DATABASE failed because a lock could not be placed on database 'MyDB'. Try again later.
ALTER DATABASE statement failed.
Cannot drop database "MyDB" because it is currently in use.
I tried this:
我试过这个：
ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
but I still cannot drop the database. (My guess is that most of the developers have dboaccess.)
但我仍然无法删除数据库。（我的猜测是大多数开发人员都dbo可以访问。）
I can manually run SP_WHOand start killing connections, but I need an automatic way to do this in the auto build. (Though this time my connection is the only one on the db I am trying to drop.)
我可以手动运行SP_WHO并开始终止连接，但我需要一种在自动构建中自动执行此操作的方法。（虽然这次我的连接是我试图断开的数据库上唯一的连接。）
Is there a script that can drop my database regardless of who is connected?
是否有一个脚本可以删除我的数据库而不管谁连接？
回答by AlexK
Updated
更新
For MS SQL Server 2012 and above
对于 MS SQL Server 2012 及更高版本
USE [master];
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = db_id('MyDB')
EXEC(@kill);
For MS SQL Server 2000, 2005, 2008
对于 MS SQL Server 2000、2005、2008
USE master;
DECLARE @kill varchar(8000); SET @kill = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('MyDB')
EXEC(@kill);
回答by Chains
USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
Ref: http://msdn.microsoft.com/en-us/library/bb522682%28v=sql.105%29.aspx
参考：http: //msdn.microsoft.com/en-us/library/bb522682%28v=sql.105%29.aspx
回答by Pourya
You can get the script that SSMS provides by doing the following:
您可以通过执行以下操作来获取 SSMS 提供的脚本：
- Right-click on a database in SSMS and choose delete
- In the dialog, check the checkbox for "Close existing connections."
- Click the Script button at the top of the dialog.
- 右键单击 SSMS 中的数据库并选择删除
- 在对话框中，选中“关闭现有连接”复选框。
- 单击对话框顶部的脚本按钮。
The script will look something like this:
该脚本将如下所示：
USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [YourDatabaseName]
GO
回答by Sodoshi
Little known: the GO sql statement can take an integer for the number of times to repeat previous command.
鲜为人知：GO sql 语句可以取整数表示重复上一条命令的次数。
So if you:
所以如果你：
ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO
Then:
然后：
USE [DATABASENAME]
GO 2000
This will repeat the USE command 2000 times, force deadlock on all other connections, and take ownership of the single connection. (Giving your query window sole access to do as you wish.)
这将重复 USE 命令 2000 次，在所有其他连接上强制死锁，并获得单个连接的所有权。（为您的查询窗口提供唯一的访问权限，让您可以按照自己的意愿进行操作。）
回答by Sacha
To my experience, using SINGLE_USER helps most of the times, however, one should be careful: I have experienced occasions in which between the time I start the SINGLE_USER command and the time it is finished... apparently another 'user' had gotten the SINGLE_USER access, not me. If that happens, you're in for a tough job trying to get the access to the database back (in my case, it was a specific service running for a software with SQL databases that got hold of the SINGLE_USER access before I did).
What I think should be the most reliable way (can't vouch for it, but it is what I will test in the days to come), is actually:
- stop services that may interfere with your access (if there are any)
- use the 'kill' script above to close all connections
- set the database to single_user immediately after that
- then do the restore
根据我的经验，使用 SINGLE_USER 在大多数情况下都有帮助，但是，应该小心：我曾经历过这样的情况，在我启动 SINGLE_USER 命令和它完成之间的时间......显然另一个“用户”已经得到了SINGLE_USER 访问，不是我。如果发生这种情况，您将面临一项艰巨的工作，试图重新获得对数据库的访问权（在我的情况下，这是为具有 SQL 数据库的软件运行的特定服务，在我之前获得了 SINGLE_USER 访问权）。我认为应该是最可靠的方式（不能保证，但这是我将在未来几天测试的）实际上是：
- 停止可能干扰您访问的服务（如果有的话）
-使用上面的“kill”脚本关闭所有连接
- 之后立即将数据库设置为 single_user
- 然后进行还原
回答by Chris Bates
Matthew's supremely efficient script updated to use the dm_exec_sessions DMV, replacing the deprecated sysprocesses system table:
Matthew 极其高效的脚本已更新为使用 dm_exec_sessions DMV，替换已弃用的 sysprocesses 系统表：
USE [master];
GO
DECLARE @Kill VARCHAR(8000) = '';
SELECT
@Kill = @Kill + 'kill ' + CONVERT(VARCHAR(5), session_id) + ';'
FROM
sys.dm_exec_sessions
WHERE
database_id = DB_ID('<YourDB>');
EXEC sys.sp_executesql @Kill;
Alternative using WHILE loop (if you want to process any other operations per execution):
使用 WHILE 循环的替代方法（如果您想在每次执行时处理任何其他操作）：
USE [master];
GO
DECLARE @DatabaseID SMALLINT = DB_ID(N'<YourDB>');
DECLARE @SQL NVARCHAR(10);
WHILE EXISTS ( SELECT
1
FROM
sys.dm_exec_sessions
WHERE
database_id = @DatabaseID )
BEGIN;
SET @SQL = (
SELECT TOP 1
N'kill ' + CAST(session_id AS NVARCHAR(5)) + ';'
FROM
sys.dm_exec_sessions
WHERE
database_id = @DatabaseID
);
EXEC sys.sp_executesql @SQL;
END;
回答by MellowTone
The accepted answer has the drawback that it doesn't take into consideration that a database can be locked by a connection that is executing a query that involves tables in a database other than the one connected to.
已接受的答案有一个缺点，即它没有考虑到数据库可以被执行查询的连接锁定，该查询涉及数据库中的表而不是所连接的表。
This can be the case if the server instance has more than one database and the query directly or indirectly (for example through synonyms) use tables in more than one database etc.
如果服务器实例有多个数据库，并且查询直接或间接（例如通过同义词）使用多个数据库中的表等，就会出现这种情况。
I therefore find that it sometimes is better to use syslockinfo to find the connections to kill.
因此，我发现有时最好使用 syslockinfo 来查找要终止的连接。
My suggestion would therefore be to use the below variation of the accepted answer from AlexK:
因此，我的建议是使用 AlexK 已接受答案的以下变体：
USE [master];
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), req_spid) + ';'
FROM master.dbo.syslockinfo
WHERE rsc_type = 2
AND rsc_dbid = db_id('MyDB')
EXEC(@kill);
回答by Shahriar Khazaei
You should be careful about exceptions during killing processes. So you may use this script:
在终止进程期间，您应该小心异常。所以你可以使用这个脚本：
USE master;
GO
DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'BEGIN TRY KILL ' + CONVERT(varchar(5), spid) + ';' + ' END TRY BEGIN CATCH END CATCH ;' FROM master..sysprocesses
EXEC (@kill)
回答by cantoni
@AlexK wrote a great answer. I just want to add my two cents. The code below is entirely based on @AlexK's answer, the difference is that you can specify the user and a time since the last batch was executed (note that the code uses sys.dm_exec_sessions instead of master..sysprocess):
@AlexK 写了一个很好的答案。我只想加上我的两分钱。下面的代码完全基于@AlexK 的回答，不同之处在于您可以指定用户和自上次执行批次以来的时间（注意代码使用 sys.dm_exec_sessions 而不是 master..sysprocess）：
DECLARE @kill varchar(8000);
set @kill =''
select @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' from sys.dm_exec_sessions
where login_name = 'usrDBTest'
and datediff(hh,login_time,getdate()) > 1
--and session_id in (311,266)
exec(@kill)
In this example only the process of the user usrDBTest which the last batch was executed more than 1 hour ago will be killed.
在这个例子中，只有最后一个批处理超过 1 小时前执行的用户 usrDBTest 的进程会被杀死。
回答by Filip Holub
You can use Cursorlike that:
您可以像这样使用Cursor：
USE master
GO
DECLARE @SQL AS VARCHAR(255)
DECLARE @SPID AS SMALLINT
DECLARE @Database AS VARCHAR(500)
SET @Database = 'AdventureWorks2016CTP3'
DECLARE Murderer CURSOR FOR
SELECT spid FROM sys.sysprocesses WHERE DB_NAME(dbid) = @Database
OPEN Murderer
FETCH NEXT FROM Murderer INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'Kill ' + CAST(@SPID AS VARCHAR(10)) + ';'
EXEC (@SQL)
PRINT ' Process ' + CAST(@SPID AS VARCHAR(10)) +' has been killed'
FETCH NEXT FROM Murderer INTO @SPID
END
CLOSE Murderer
DEALLOCATE Murderer
I wrote about that in my blog here: http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor
我在我的博客中写过这个：http: //www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor
