脚本用于终止与数据库的所有连接（超过RESTRICTED_USER ROLLBACK）
脚本用于终止与数据库的所有连接（超过RESTRICTED_USER ROLLBACK）
我有一个开发数据库，经常从一个Visual Studio数据库项目（通过TFS自动构建）重新部署。\n有时候当我运行构建时会出现以下错误：\n
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.
\n我尝试了以下方法：\n
ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
\n但我仍然无法删除数据库。（我猜测大多数开发人员都具有dbo访问权限。）\n我可以手动运行SP_WHO并开始终止连接，但我需要在自动构建中自动完成这个过程。（尽管这次我连接到的数据库上只有一个连接。）\n是否有一个脚本可以无论谁连接都能删除我的数据库？
脚本用于终止与数据库的所有连接（超过RESTRICTED_USER ROLLBACK）是由于以下原因导致的：
1. 删除数据库时选择了"关闭现有连接"选项，这会生成一个终止所有连接的脚本。
以下是SSMS提供的脚本示例：
USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [YourDatabaseName]
GO
然而，我不建议以单用户模式运行数据库，因为这可能导致您失去与某些应用程序用户的当前连接，并且不必要地麻烦来查找这些用户并终止相同的连接，有时甚至需要重新启动SQL Server，如果对数据库的连接非常频繁。
要解决这个问题，可以尝试以下方法：
1. 在删除数据库之前，先检查是否有其他用户连接到该数据库，并要求他们断开连接。
2. 如果无法联系到所有连接到数据库的用户，您可以尝试通过以下脚本终止所有连接，而无需将数据库设置为单用户模式：
USE [master]
GO
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), spid) + ';'
FROM sys.sysprocesses
WHERE dbid = DB_ID('YourDatabaseName')
EXEC(@kill);
GO
请注意，这个方法将终止与数据库相关的所有连接，包括活动的和非活动的连接。确保在执行此脚本之前备份数据库以防万一。
使用上述方法可以避免将数据库设置为单用户模式，并且可以终止所有连接，而不会对其他用户造成不必要的麻烦。
在这个问题中，用户想要杀死所有连接到一个数据库的连接，但是他在执行脚本时遇到了问题。下面是问题的出现原因和解决方法的整理：
问题原因：
- 用户在脚本中没有先切换到master数据库，而是直接操作目标数据库。
- 用户在尝试删除数据库时仍然连接到该数据库。
解决方法：
- 在执行任何数据库操作之前，先切换到master数据库。
- 使用SET OFFLINE WITH ROLLBACK IMMEDIATE语句将数据库设置为离线状态，并立即回滚未完成的事务。
- 如果使用SET OFFLINE语句，需要手动删除数据库文件。
- 使用ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE语句可以更好地解决问题。这会将数据库设置为单用户模式，并立即回滚未完成的事务。这样，用户的连接将成为唯一的连接，并且DROP DATABASE YourDatabaseName语句仍然可以删除数据库文件。
- 在脚本中，用户并没有连接到数据库，所以不是用户的连接被保留，而是其他连接被保留。在SET OFFLINE之后，可以使用SET ONLINE语句来避免文件残留的问题（是的，这可能存在竞态条件）。
其他相关信息：
- 有用户反馈，在SQL Management Studio中，之前执行在该数据库上的某个SQL语句导致数据库被认为正在使用中。在执行脚本之前，切换到master数据库并执行GO语句可以解决这个问题。
- 实际上，即使使用单用户模式也存在一些问题：它确保没有其他“普通”用户可以连接，但是诸如自动异步更新统计信息等内置进程并没有被禁用，当它们运行时，它们会占用单个连接，终止你的进程。如果有任何可能运行的异步进程，就没有可靠的方法来强制执行单用户模式，但是SET OFFLINE是有效的。我在我们的CI上吃了这个亏，我们经常使用单用户模式作为模式迁移测试的一部分。
问题的出现原因：需要终止数据库的所有连接，但是使用的已有方法无法完全回滚所有连接。
解决方法：通过执行特定的SQL语句来终止数据库的所有连接。
对于SQL Server 2012及以上版本：
USE [master];
DECLARE @sql varchar(8000) = '';
SELECT @sql = @sql + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = db_id('MyDB')
EXEC(@sql);
对于SQL Server 2000、2005、2008版本：
USE master;
DECLARE @sql varchar(8000); SET @sql = '';
SELECT @sql = @sql + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('MyDB')
EXEC(@sql);
这是两种方法中更好的答案，避免了将数据库脱机，而且接受的答案并不总是有效（有时无法完全回滚）。
这个答案非常好地聚合了所有要终止的连接的kill语句。我会使用游标来逐个终止每个进程，这当然不是高效的。这个答案使用的技术非常出色。
我同意Mark的观点。这个方法应该被接受为答案，因为它更加优雅，对数据库的影响更小。
好的方法，非常快速。唯一的问题可能是系统SPID，所以可以添加WHERE dbid = db_id('My_db') and spid > 50。
如何解决“无法终止自己的进程”问题？我必须打开一个查询窗口来执行新的查询。所以我猜这就是“我自己的进程”，我的连接到数据库。
在运行脚本之前，你需要改变数据库的上下文。例如：USE [Master]。
这是我需要的，我认为这是更好的答案。
我完全按照这个代码来执行，但是出现错误：“Incorrect syntax near 'Go'.”。有人知道为什么会发生这种情况吗？
在SQL Server 2008中，database_id列是不可用的。它仅在SQL Server 2012及更高版本中可用。参考MSDN了解更多信息。
SQL Server 2000/2005的脚本不起作用，因为在声明变量的同时不能给它赋值（这个功能是在2008中引入的）。将第一行更改为DECLARE @sql varchar(8000)并添加SET @sql = ''即可解决。
在WHERE子句中添加AND session_id!=@以避免“终止”自己的进程...
不得不重新启动所有的域。
字符串的聚合拼接（ = + …）不受支持，可能会产生意外的结果。最好使用FOR XML PATH或在SQL 2017及更高版本中使用STRING_AGG进行重构。
我尝试了这个方法，但是出现“Only user processes can be killed.”的错误消息。我应该怎么办？
你可以尝试在WHERE子句中添加WHERE dbid = db_id('My_db') and spid > 50。
我想终止除了我的连接之外的所有连接。在WHERE子句中添加and session_id != @。
在我的情况下，我使用的是SQL Server 14.0和Management Studio 17.9.1，而且我必须说这是唯一一个对我有效的答案。我想要删除一些满足条件的数据库（如“name LIKE 'DBName_%'”），但是这些数据库都在使用中。一些服务在使用它们，所以我想先终止活动连接。你的答案帮助了我！谢谢！