blocks|key|1567710|text|Adam+suggested方法不起作用的原因是，在您循环活动连接的过程中，可能会建立新的连接，而您会错过这些连接。您可以改用以下方法，该方法没有此缺点：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1567711|--+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|code-block|syntax|javascript|1567712|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/11620/how-do-you-kill-all-current-connections-to-a-sql-server-2005-database/11627#11627^0|0|E|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

The reason that the approach that <a href="https://stackoverflow.com/questions/11620/how-do-you-kill-all-current-connections-to-a-sql-server-2005-database/11627#11627">Adam suggested</a> 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. You could instead use the following approach which does not have this drawback:

<pre><code>-- 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
</code></pre>

blocks|key|1567754|text|要完成此操作，请将'DB_NAME‘替换为要终止所有连接的数据库：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1567755|USE+master
GO

SET+NOCOUNT+ON
DECLARE+@DBName+varchar(50)
DECLARE+@spidstr+varchar(8000)
DECLARE+@ConnKilled+smallint
SET+@ConnKilled=0
SET+@spidstr+=+''

Set+@DBName+=+'DB_NAME'
IF+db_id(@DBName)+<+4
BEGIN
PRINT+'Connections+to+system+databases+cannot+be+killed'
RETURN
END
SELECT+@spidstr=coalesce(@spidstr,','+)%2B'kill+'%2Bconvert(varchar,+spid)%2B+';+'
FROM+master..sysprocesses+WHERE+dbid=db_id(@DBName)

IF+LEN(@spidstr)+>+0
BEGIN
EXEC(@spidstr)
SELECT+@ConnKilled+=+COUNT(1)
FROM+master..sysprocesses+WHERE+dbid=db_id(@DBName)
END|code-block|syntax|javascript|1567756|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Script to accomplish this, replace 'DB_NAME' with the database to kill all connections to:

<pre><code>USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DB_NAME'
IF db_id(@DBName) &lt; 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) &gt; 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
</code></pre>

blocks|key|1782899|text|杀了它，用火烧死它：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1782900|USE+master
go

DECLARE+@dbname+sysname
SET+@dbname+=+'yourdbname'

DECLARE+@spid+int
SELECT+@spid+=+min(spid)+from+master.dbo.sysprocesses+where+dbid+=+db_id(@dbname)
WHILE+@spid+IS+NOT+NULL
BEGIN
EXECUTE+('KILL+'+%2B+@spid)
SELECT+@spid+=+min(spid)+from+master.dbo.sysprocesses+where+dbid+=+db_id(@dbname)+AND+spid+>+@spid
END|code-block|syntax|javascript|1782901|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Kill it, and kill it with fire:

<pre><code>USE master
go

DECLARE @dbname sysname
SET @dbname = 'yourdbname'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid &gt; @spid
END
</code></pre>

blocks|key|1567822|text|使用SQL+Management+Studio+Express：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1567823|在对象资源管理器树中，在Management下钻取到"Activity+Monitor“(如果您在那里找不到它，则右键单击数据库服务器并选择"Activity+Monitor")。打开活动监视器，您可以查看所有进程信息。您应该能够找到您感兴趣的数据库的锁，并终止这些锁，这也会终止连接。|1567824|在此之后，您应该能够重命名。|1567825|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Using SQL Management Studio Express:

In the Object Explorer tree drill down under Management to "Activity Monitor" (if you cannot find it there then right click on the database server and select "Activity Monitor"). Opening the Activity Monitor, you can view all process info. You should be able to find the locks for the database you're interested in and kill those locks, which will also kill the connection.

You should be able to rename after that.

blocks|key|1567848|text|我一直在用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1567849|ALTER+DATABASE+DB_NAME+SET+SINGLE_USER+WITH+ROLLBACK+IMMEDIATE+
GO+
SP_RENAMEDB+'DB_NAME','DB_NAME_NEW'
Go+
ALTER+DATABASE+DB_NAME_NEW++SET+MULTI_USER+--+set+back+to+multi+user+
GO+|code-block|syntax|javascript|1567850|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I've always used:

<pre><code>
ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO 
SP_RENAMEDB 'DB_NAME','DB_NAME_NEW'
Go 
ALTER DATABASE DB_NAME_NEW SET MULTI_USER -- set back to multi user 
GO 
</code></pre>

blocks|key|1783295|text|ALTER+DATABASE+[Test]
SET+OFFLINE+WITH+ROLLBACK+IMMEDIATE

ALTER+DATABASE+[Test]
SET+ONLINE|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1783296|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>ALTER DATABASE [Test]
SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Test]
SET ONLINE
</code></pre>

blocks|key|1568204|text|Select+'Kill+'%2B+CAST(p.spid+AS+VARCHAR)KillCommand+into+#temp
from+master.dbo.sysprocesses+p+(nolock)
join+master..sysdatabases+d+(nolock)+on+p.dbid+=+d.dbid
Where+d.[name]+=+'your+db+name'

Declare+@query+nvarchar(max)
--Select+*+from+#temp
Select+@query+=STUFF((++++++++++++++++++++++++++++++
++++++++++++select+'++'+%2B+KillCommand+from+#temp
++++++++++++FOR+XML+PATH('')),1,1,'')+
Execute+sp_executesql+@query+
Drop+table+#temp|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1568205|使用'master‘数据库并运行此查询，它将杀死数据库中的所有活动连接。|unstyled|1568206|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>Select 'Kill '+ CAST(p.spid AS VARCHAR)KillCommand into #temp
from master.dbo.sysprocesses p (nolock)
join master..sysdatabases d (nolock) on p.dbid = d.dbid
Where d.[name] = 'your db name'

Declare @query nvarchar(max)
--Select * from #temp
Select @query =STUFF(( 
 select ' ' + KillCommand from #temp
 FOR XML PATH('')),1,1,'') 
Execute sp_executesql @query 
Drop table #temp
</code></pre>

use the 'master' database and run this query, it will kill all the active connections from your database.

blocks|key|1567915|text|在尝试还原数据库时，我通常会遇到该错误。我通常只是转到Management+Studio中树的顶部，然后右键单击并重新启动数据库服务器(因为它位于开发计算机上，这在生产环境中可能并不理想)。这是关闭所有数据库连接。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1567916|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I usually run into that error when I am trying to restore a database I usually just go to the top of the tree in Management Studio and right click and restart the database server (because it's on a development machine, this might not be ideal in production). This is close all database connections.

blocks|key|1567625|text|在对象资源管理器上的MS+SQL+Server+Management+Studio中，右键单击数据库。在随后的上下文菜单中，选择'Tasks+->+Take+Offline‘|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1567626|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

In MS SQL Server Management Studio on the object explorer, right click on the database. In the context menu that follows select 'Tasks -> Take Offline'

blocks|key|1568138|text|另一种“杀鸡取卵”的方法是重新启动MSSQLSERVER服务。我喜欢从命令行做事情。将其粘贴到CMD中就可以了:+NET+STOP+MSSQLSERVER+&+NET+START+MSSQLSERVER|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1568139|或者打开"services.msc“，找到"SQL+Server+(MSSQLSERVER)”，右击，选择“重新启动”。|1568140|这将“肯定地，肯定地”杀死到该实例上运行的所有数据库的所有连接。|1568141|(与更改服务器/数据库上的配置并重新更改的许多方法相比，我更喜欢这种方法)|1568142|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Another "kill it with fire" approach is to just restart the MSSQLSERVER service.
I like to do stuff from the commandline. Pasting this exactly into CMD will do it:
NET STOP MSSQLSERVER &amp; NET START MSSQLSERVER

Or open "services.msc" and find "SQL Server (MSSQLSERVER)" and right-click, select "restart".

This will "for sure, for sure" kill ALL connections to ALL databases running on that instance.

(I like this better than many approaches that change and change back the configuration on the server/database)

blocks|key|1567837|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1567838|ALTER+DATABASE+[DATABASE_NAME]
SET+SINGLE_USER
WITH+ROLLBACK+IMMEDIATE|code-block|syntax|javascript|1567839|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
</code></pre>

blocks|key|1782971|text|右键单击数据库名称，单击Property+to+get+property窗口，打开Options选项卡，将"Restrict“属性从多用户更改为单用户。当你点击确定按钮，它将提示你关闭所有打开的连接，选择“是”，你被设置为重命名数据库...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1782972|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Right click on the database name, click on Property to get property window, Open the Options tab and change the "Restrict Access" property from Multi User to Single User. When you hit on OK button, it will prompt you to closes all open connection, select "Yes" and you are set to rename the database....

blocks|key|1568267|text|这些对我(SQL2008企业版)不起作用，我也看不到任何正在运行的进程或连接到数据库的用户。重新启动服务器(右键单击Management+Studio中的Sql+server并选择重新启动)允许我恢复数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1568268|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

These didn't work for me (SQL2008 Enterprise), I also couldn't see any running processes or users connected to the DB. Restarting the server (Right click on Sql Server in Management Studio and pick Restart) allowed me to restore the DB.

blocks|key|1783283|text|我使用的是SQL+Server2008+R2，我的数据库已经设置为单用户，并且有一个连接限制了对数据库的任何操作。因此，推荐的SQLMenace's解决方案返回错误。Here+is+one+that+worked+in+my+case。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1783284|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/11624/1451048|1|http://blog.tech-cats.com/2008/01/kill-all-database-connections-to-sql.html^0|1R|B|0|2B|Y|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

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. Thus the recommended <a href="https://stackoverflow.com/a/11624/1451048">SQLMenace's</a> solution responded with error. <a href="http://blog.tech-cats.com/2008/01/kill-all-database-connections-to-sql.html" rel="nofollow noreferrer">Here is one that worked in my case</a>.

blocks|key|1783339|text|您可以使用SP_Who命令杀死使用您数据库的所有进程，然后重命名数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1783340|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You can Use SP_Who command and kill all process that use your database and then rename your 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?

How do you kill all current connections to a SQL Server 2005 database?

SQL Server

数据库

服务器

我想要重命名一个数据库，但一直收到“无法获得数据库独占锁”的错误，这意味着仍有一些连接处于活动状态。如何删除数据库的所有连接，以便重命名数据库？

问如何终止与SQL Server2005数据库的所有当前连接？
EN

回答 15

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何终止与SQL Server2005数据库的所有当前连接？EN

回答 15

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何终止与SQL Server2005数据库的所有当前连接？
EN