首页 / 知识

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

2023-04-12 05:52:00

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

我想重命名数据库，但不断收到错误消息"数据库无法获得排他锁"，这意味着仍有一些连接处于活动状态。

如何终止与数据库的所有连接，以便可以对其重命名？

请参阅终止与数据库的所有活动连接。

Adam建议的方法行不通的原因是，在循环活动连接的过程中，可以建立新的连接，而您会错过这些连接。我链接到的文章使用以下方法，但没有此缺点：

1
2
3
4
5
6
7
8

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

为此，请用脚本替换" DB_NAME"以终止与以下数据库的所有连接：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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

杀死它，然后用火杀死它：

1
2
3
4
5
6
7
8
9
10
11
12
13

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

使用SQL Management Studio Express：

在"对象资源管理器"树中，在"管理"下向下钻取到"活动监视器"(如果在那里找不到它，则右键单击数据库服务器并选择"活动监视器")。打开活动监视器，您可以查看所有过程信息。您应该能够找到您感兴趣的数据库的锁，并杀死这些锁，这也会终止连接。

之后，您应该可以重命名。

我一直使用：

1
2
3
4
5
6

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

1
2
3
4
5

ALTER DATABASE [Test]
SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Test]
SET ONLINE

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

我认为最可靠的方法是：

分离
右键单击数据库->任务->分离...
检查"拖放连接"
好

重新连接
右键单击数据库->附加。
添加...->选择数据库，然后将"附加为"列更改为所需的数据库名称。
好

1
2
3
4
5
6
7
8
9
10
11
12

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

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

当我尝试还原数据库时，通常会遇到该错误，我通常只是在Management Studio中移至树的顶部，然后右键单击并重新启动数据库服务器(因为它在开发机器上，因此在生产中可能并不理想) )。这将关闭所有数据库连接。

在对象浏览器上的MS SQL Server Management Studio中，右键单击数据库。在随后的上下文菜单中，选择"任务->脱机"

这是在MS SQL Server Management Studio 2008中可靠地进行此类操作的方法(也可能适用于其他版本)：

在"对象资源管理器树"中，右键单击根数据库服务器(带有绿色箭头)，然后单击"活动监视器"。

在活动监视器中打开"进程"选项卡，选择"数据库"下拉菜单，然后按所需的数据库进行过滤。

在对象资源管理器中右键单击数据库，然后启动"任务->脱机"任务。在您执行以下操作时，让它在后台运行

安全关闭所有可能的工具。

从"进程"选项卡中杀死所有剩余的进程。

使数据库重新联机。

重命名数据库。

使您的服务重新联机，然后将其指向新的数据库。

另一种"用火杀死它"的方法是仅重新启动MSSQLSERVER服务。
我喜欢从命令行执行操作。将其完全粘贴到CMD中即可：
NET STOP MSSQLSERVER和NET START MSSQLSERVER

或打开" services.msc"并找到" SQL Server(MSSQLSERVER)"，然后单击鼠标右键，选择"重新启动"。

这将"确定，确定"终止与该实例上运行的所有数据库的所有连接。

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

在这种情况下为我工作的选项如下：

在有问题的数据库上启动"分离"操作。这将打开一个窗口(在SQL 2005中)，该窗口显示阻止数据库操作的活动连接。

终止活动连接，取消分离操作。

该数据库现在应该可以还原了。

尝试这个：

1
2
3

ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

这些对我不起作用(SQL2008 Enterprise)，我也看不到任何正在运行的进程或连接到数据库的用户。重新启动服务器(在Management Studio中右键单击Sql Server，然后选择"重新启动")使我可以还原数据库。

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

右键单击数据库名称，单击"属性"以获取属性窗口，打开"选项"选项卡，然后将"限制访问"属性从"多用户"更改为"单用户"。当您单击"确定"按钮时，它将提示您关闭所有打开的连接，选择"是"，然后您将设置为重命名数据库。

我使用sp_who获取数据库中所有进程的列表。这样比较好，因为您可能需要查看要终止的进程。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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

结果
您可以在KillCommand列中使用command杀死想要的进程。

1
2
3
4

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

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

数据库连接重命名消息