如何终止当前与SQL Server 2005数据库的所有连接？

本文翻译自：How do you kill all current connections to a SQL Server 2005 database?

I want to rename a database, but keep getting the error that 'couldn't get exclusive lock' on the database, which implies there is some connection(s) still active. 我想重命名数据库，但不断收到错误消息“数据库无法获得排他锁”，这意味着仍有一些连接处于活动状态。

How can I kill all the connections to the database so that I can rename it? 如何终止与数据库的所有连接，以便可以对其重命名？

---

#1楼

参考：https://stackoom.com/question/31q/如何终止当前与SQL-Server-数据库的所有连接

---

#2楼

These didn't work for me (SQL2008 Enterprise), I also couldn't see any running processes or users connected to the DB. 这些对我不起作用（SQL2008 Enterprise），我也看不到任何正在运行的进程或连接到数据库的用户。 Restarting the server (Right click on Sql Server in Management Studio and pick Restart) allowed me to restore the DB. 重新启动服务器（在Management Studio中右键单击Sql Server，然后选择“重新启动”）使我可以还原数据库。

---

#3楼

I'm using SQL Server 2008 R2, my DB was already set for single user and there was a connection that restricted any action on the database. 我正在使用SQL Server 2008 R2，我的数据库已经为单个用户设置，并且存在一个连接，该连接限制了对数据库的任何操作。 Thus the recommended SQLMenace's solution responded with error. 因此，推荐的SQLMenace解决方案响应错误。 Here is one that worked in my case . 这是对我来说有效的一种 。

---

#4楼

ALTER DATABASE [Test]
SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Test]
SET ONLINE

---

#5楼

In MS SQL Server Management Studio on the object explorer, right click on the database. 在对象浏览器上的MS SQL Server Management Studio中，右键单击数据库。 In the context menu that follows select 'Tasks -> Take Offline' 在随后的上下文菜单中，选择“任务->脱机”

---

#6楼

The reason that the approach that Adam suggested won't work is that during the time that you are looping over the active connections new one can be established, and you'll miss those. Adam建议的方法行不通的原因是，在循环活动连接的过程中，可以建立新的连接，而您会错过这些连接。 You could instead use the following approach which does not have this drawback: 您可以改用以下方法，但没有此缺点：

-- set your current connection to use master otherwise you might get an error

use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

--do you stuff here 

ALTER DATABASE YourDatabase SET MULTI_USER