我有一个经常从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并开始终止连接,但我需要在自动构建中自动执行此操作。(虽然这次我的连接是数据库中唯一一个我试图放弃的连接。)

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


当前回答

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

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

脚本看起来像这样:

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

其他回答

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;

@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小时前执行的进程会被杀死。

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

所以如果你:

ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO

然后:

USE [DATABASENAME]
GO 2000

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

你可以像这样使用光标:

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

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

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

因此,我发现有时最好使用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);