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

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?

我怎样才能删除数据库的所有连接，以便重命名它?

19 个解决方案

#1

See Kill All Active Connections To A Database.

请参见关闭数据库的所有活动连接。

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. The article I linked to uses the following approach which does not have this drawback:

Adam建议的方法不起作用的原因是，当你在活动连接上循环时，可以建立一个新的连接，你会错过这些。我链接到的文章使用了以下方法，没有这个缺点:

-- 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

#2

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

要实现此目的，请将'DB_NAME'替换为数据库，以杀死以下所有连接:

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,',' )+'kill '+convert(varchar, spid)+ '; '
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

#3

Kill it, and kill it with fire:

杀死它，用火杀死它:

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 > @spid
END

#4

Using SQL Management Studio Express:

使用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.

您应该能够在那之后重命名。

#5

I've always used:

我一直使用:

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 

#6

ALTER DATABASE [Test]
SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Test]
SET ONLINE

#7

Take offline takes a while and sometimes I experience some problems with that..

离线需要一段时间，有时我会遇到一些问题。

Most solid way in my opinion:

我认为最可靠的方法是:

Detach Right click DB -> Tasks -> Detach... check "Drop Connections" Ok

Detach右击DB ->任务-> Detach…检查“连接”好

Reattach Right click Databases -> Attach.. Add... -> select your database, and change the Attach As column to your desired database name. Ok

重新附加右键单击数据库->附加。添加……->选择您的数据库，并将附加作为列更改为所需的数据库名称。好吧

#8

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

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

使用“主”数据库并运行此查询，它将杀死数据库中的所有活动连接。

#9

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.

当我试图还原一个数据库时，我通常会在Management Studio中找到树的顶端，右键单击并重新启动数据库服务器(因为它在开发机器上，这在生产中可能不太理想)。这将关闭所有数据库连接。

#10

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'

在object explorer的MS SQL Server Management Studio中，右键单击数据库。在选择“任务->脱机”的上下文菜单中

#11

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 & NET START MSSQLSERVER

另一种“用火杀死它”的方法是重新启动MSSQLSERVER服务。我喜欢做命令行里的事情。将其粘贴到CMD中即可:NET STOP MSSQLSERVER & NET START MSSQLSERVER

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

或开放”服务。找到“SQL Server (MSSQLSERVER)”，右键单击，选择“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)

(与许多更改和更改服务器/数据库配置的方法相比，我更喜欢这种方法)

#12

Here's how to reliably this sort of thing in MS SQL Server Management Studio 2008 (may work for other versions too):

以下是如何在MS SQL Server Management Studio 2008(可能也适用于其他版本)中可靠地实现这种功能的方法:

1. In the Object Explorer Tree, right click the root database server (with the green arrow), then click activity monitor.
2. 在对象资源管理器树中，右键单击根数据库服务器(使用绿色箭头)，然后单击活动监视器。
3. Open the processes tab in the activity monitor, select the 'databases' drop down menu, and filter by the database you want.
4. 在活动监视器中打开process选项卡，选择“database”下拉菜单，然后根据需要的数据库进行筛选。
5. Right click the DB in Object Explorer and start a 'Tasks -> Take Offline' task. Leave this running in the background while you...
6. 右键单击Object Explorer中的DB并启动“任务-> Take Offline”任务。当你…
7. Safely shut down whatever you can.
8. 尽你所能，安全地关闭一切。
9. Kill all remaining processes from the process tab.
10. 从process选项卡中删除所有剩余的进程。
11. Bring the DB back online.
12. 把数据库带回到网上。
13. Rename the DB.
14. 重命名DB。
15. Bring your service back online and point it to the new DB.
16. 将您的服务恢复到在线并指向新的DB。

#13

The option working for me in this scenario is as follows:

在这种情况下，我的选择如下:

1. Start the "Detach" operation on the database in question. This wil open a window (in SQL 2005) displaying the active connections that prevents actions on the DB.
2. 在相关的数据库上启动“Detach”操作。这将打开一个窗口(在SQL 2005中)，显示阻止在DB上操作的活动连接。
3. Kill the active connections, cancel the detach-operation.
4. 关闭活动连接，取消分离操作。
5. The database should now be available for restoring.
6. 现在应该可以使用数据库进行恢复。

#14

Try this:

试试这个:

ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

#15

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....

右键单击数据库名称，单击属性以获取属性窗口，打开Options选项卡并将“限制访问”属性从多用户更改为单个用户。当你点击OK按钮时,会提示你关闭所有打开的连接,选择“是”,您将重命名数据库....

#16

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.

这些对我来说并不适用(SQL2008 Enterprise)，我也看不到任何正在运行的进程或连接到DB的用户。重新启动服务器(在Management Studio中右键单击Sql server并选择Restart)允许我恢复DB。

#17

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 SQLMenace's solution responded with error. Here is one that worked in my case.

我正在使用SQL Server 2008 R2，我的DB已经为单个用户设置好了，并且有一个连接限制了数据库上的任何操作。因此，推荐的sqlthreat解决方案的响应是错误的。这里有一个在我的案例中起作用。

#18

I use sp_who to get list of all process in database. This is better because you may want to review which process to kill.

我使用sp_who获取数据库中所有进程的列表。这更好，因为您可能想要检查要杀死哪个过程。

declare @proc table(
    SPID bigint,
    Status nvarchar(255),
    Login nvarchar(255),
    HostName nvarchar(255),
    BlkBy nvarchar(255),
    DBName nvarchar(255),
    Command nvarchar(MAX),
    CPUTime bigint,
    DiskIO bigint,
    LastBatch nvarchar(255),
    ProgramName nvarchar(255),
    SPID2 bigint,
    REQUESTID bigint
)

insert into @proc
exec sp_who2

select  *, KillCommand = concat('kill ', SPID, ';')
from    @proc

Result
You can use command in KillCommand column to kill the process you want to.

结果您可以在KillCommand列中使用命令来终止您想要的进程。

SPID    KillCommand
26      kill 26;
27      kill 27;
28      kill 28;

#19

You can Use SP_Who command and kill all process that use your database and then rename your database.

可以使用SP_Who命令并杀死使用数据库的所有进程，然后重命名数据库。