终止数据库的所有连接(大于RESTRICTED_USER ROLLBACK)的脚本

我有一个经常从Visual Studio数据库项目重新部署的开发数据库(通过TFS自动构建)。

有时当我运行我的构建时，我会得到这个错误:

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.

我试了一下:

ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

但我仍然无法删除数据库。(我猜大多数开发人员都有dbo访问权限。)

我可以手动运行SP_WHO并开始终止连接，但我需要在自动构建中自动执行此操作。(虽然这次我的连接是数据库中唯一一个我试图放弃的连接。)

是否有一个脚本可以删除我的数据库，而不管连接的是谁?

706300 次浏览
小开 
USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

裁判:http://msdn.microsoft.com/en-us/library/bb522682%28v=sql.105%29.aspx

小开
最佳答案

更新

适用于MS SQL Server 2012及以上版本

USE [master];


DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDB')


EXEC(@kill);

对于MS SQL Server 2000, 2005, 2008

USE master;


DECLARE @kill varchar(8000); SET @kill = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('MyDB')


EXEC(@kill);
小开

您可以通过以下方法获取SSMS提供的脚本:

  1. 右键单击SSMS中的数据库，选择“删除”
  2. 在对话框中，选中“关闭现有连接”复选框。
  3. 单击对话框顶部的Script按钮。

脚本看起来像这样:

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [YourDatabaseName]
GO
小开

我已经用下面的简单代码成功地进行了测试

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
小开
根据我的经验，使用SINGLE_USER在大多数情况下都有帮助，但是，人们应该小心:我有过这样的经历，在我启动SINGLE_USER命令的时间和它完成的时间之间……显然，另一个“用户”获得了SINGLE_USER访问权限，而不是我。如果发生这种情况，您将面临一项艰难的工作，试图重新获得对数据库的访问权(在我的例子中，它是为一个带有SQL数据库的软件运行的特定服务，它在我之前获得了SINGLE_USER访问权)。 我认为最可靠的方法(不能保证，但我会在接下来的日子里进行测试)实际上是:
—停止可能影响您访问的服务(如果有)
-使用'kill'脚本关闭所有
的连接 —在
后立即设置数据库为single_user -然后恢复

小开

鲜为人知的是:GO sql语句可以接受一个整数来表示重复上一个命令的次数。

所以如果你:

ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO

然后:

USE [DATABASENAME]
GO 2000

这将重复使用USE命令2000次，在所有其他连接上强制死锁，并获得单个连接的所有权。(给你的查询窗口单独访问做你想做的事。)

小开

Matthew的超级高效脚本更新为使用dm_exec_sessions DMV，取代了废弃的sysprocesses系统表:

USE [master];
GO


DECLARE @Kill VARCHAR(8000) = '';


SELECT
@Kill = @Kill + 'kill ' + CONVERT(VARCHAR(5), session_id) + ';'
FROM
sys.dm_exec_sessions
WHERE
database_id = DB_ID('<YourDB>');


EXEC sys.sp_executesql @Kill;

替代使用WHILE循环(如果你想每次执行处理任何其他操作):

USE [master];
GO


DECLARE @DatabaseID SMALLINT = DB_ID(N'<YourDB>');
DECLARE @SQL NVARCHAR(10);


WHILE EXISTS ( SELECT
1
FROM
sys.dm_exec_sessions
WHERE
database_id = @DatabaseID )
BEGIN;
SET @SQL = (
SELECT TOP 1
N'kill ' + CAST(session_id AS NVARCHAR(5)) + ';'
FROM
sys.dm_exec_sessions
WHERE
database_id = @DatabaseID
);
EXEC sys.sp_executesql @SQL;
END;
小开

在终止进程期间，应该小心处理异常。所以你可以使用这个脚本:

USE master;
GO
DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'BEGIN TRY KILL ' + CONVERT(varchar(5), spid) + ';' + ' END TRY BEGIN CATCH END CATCH ;' FROM master..sysprocesses
EXEC (@kill)
小开

@AlexK写了一个很棒的回答。我只是想补充我的意见。下面的代码完全基于@AlexK的回答，不同之处在于，您可以指定用户和自最后一批执行以来的时间(注意，代码使用sys. exe。Dm_exec_sessions代替master..sysprocess):

DECLARE @kill varchar(8000);
set @kill =''
select @kill = @kill + 'kill ' +  CONVERT(varchar(5), session_id) + ';' from sys.dm_exec_sessions
where login_name = 'usrDBTest'
and datediff(hh,login_time,getdate()) > 1
--and session_id in (311,266)
exec(@kill)

在这个例子中，只有用户usrDBTest在1小时前执行的进程会被杀死。

小开

你可以像这样使用光标:

USE master
GO


DECLARE @SQL AS VARCHAR(255)
DECLARE @SPID AS SMALLINT
DECLARE @Database AS VARCHAR(500)
SET @Database = 'AdventureWorks2016CTP3'


DECLARE Murderer CURSOR FOR
SELECT spid FROM sys.sysprocesses WHERE DB_NAME(dbid) = @Database


OPEN Murderer


FETCH NEXT FROM Murderer INTO @SPID
WHILE @@FETCH_STATUS = 0


BEGIN
SET @SQL = 'Kill ' + CAST(@SPID AS VARCHAR(10)) + ';'
EXEC (@SQL)
PRINT  ' Process ' + CAST(@SPID AS VARCHAR(10)) +' has been killed'
FETCH NEXT FROM Murderer INTO @SPID
END


CLOSE Murderer
DEALLOCATE Murderer

我在我的博客里写过: http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor < / p >

小开 
SELECT
spid,
sp.[status],
loginame [Login],
hostname,
blocked BlkBy,
sd.name DBName,
cmd Command,
cpu CPUTime,
memusage Memory,
physical_io DiskIO,
lastwaittype LastWaitType,
[program_name] ProgramName,
last_batch LastBatch,
login_time LoginTime,
'kill ' + CAST(spid as varchar(10)) as 'Kill Command'
FROM master.dbo.sysprocesses sp
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name NOT IN ('master', 'model', 'msdb')
--AND sd.name = 'db_name'
--AND hostname like 'hostname1%'
--AND loginame like 'username1%'
ORDER BY spid


/* If a service connects continously. You can automatically execute kill process then run your script:
DECLARE @sqlcommand nvarchar (500)
SELECT @sqlcommand = 'kill ' + CAST(spid as varchar(10))
FROM master.dbo.sysprocesses sp
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name NOT IN ('master', 'model', 'msdb')
--AND sd.name = 'db_name'
--AND hostname like 'hostname1%'
--AND loginame like 'username1%'
--SELECT @sqlcommand
EXEC sp_executesql @sqlcommand
*/
小开

公认的答案有一个缺点，即它没有考虑到数据库可能被正在执行查询的连接所锁定，该查询涉及到所连接的数据库以外的数据库中的表。

如果服务器实例有多个数据库，并且查询直接或间接(例如通过同义词)使用多个数据库中的表等，就会出现这种情况。

因此，我发现有时最好使用syslockinfo来查找要删除的连接。

因此，我的建议是使用AlexK给出的接受答案的以下变体:

USE [master];


DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), req_spid) + ';'
FROM master.dbo.syslockinfo
WHERE rsc_type = 2
AND rsc_dbid  = db_id('MyDB')


EXEC(@kill);
小开 
USE MASTER
GO
 

DECLARE @Spid INT
DECLARE @ExecSQL VARCHAR(255)
 

DECLARE KillCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT  DISTINCT SPID
FROM    MASTER..SysProcesses
WHERE   DBID = DB_ID('dbname')
 

OPEN    KillCursor
 

-- Grab the first SPID
FETCH   NEXT
FROM    KillCursor
INTO    @Spid
 

WHILE   @@FETCH_STATUS = 0
BEGIN
SET     @ExecSQL = 'KILL ' + CAST(@Spid AS VARCHAR(50))
 

EXEC    (@ExecSQL)
 

-- Pull the next SPID
FETCH   NEXT
FROM    KillCursor
INTO    @Spid
END
 

CLOSE   KillCursor
 

DEALLOCATE  KillCursor
小开

如果你想只有删除一个数据库，你可以选择“关闭现有连接”选项。默认情况下是未设置的。

  1. 右键单击数据库目录->选项。
  2. 删除→检查选项。
  3. 好吧

enter image description here


提交答案