问答中心分类: SQL终止与数据库的所有连接的脚本(超过 RESTRICTED_USER ROLLBACK)
0
匿名用户 提问 38分钟 前

我有一个开发数据库,​​经常从 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并开始终止连接,但我需要一种自动方式在自动构建中执行此操作。 (虽然这次我的连接是我试图删除的数据库上唯一的连接。)
是否有一个脚本可以删除我的数据库,无论谁连接?

13 Answers
0
Chains 回答 38分钟 前
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

Vaccano 回复 38分钟 前

奇怪的是USE master那是关键。我试图在连接到数据库时删除它(Duh!)。谢谢!

mattalxndr 回复 38分钟 前

如果你使用SET OFFLINE您必须手动删除数据库文件。

Keith 回复 38分钟 前

不会alter database YourDatabaseName set SINGLE_USER with rollback immediate会更好?如果您将其设置为OFFLINE(如@mattalxndr 状态)文件将留在磁盘上,但SINGLE_USER您的连接将作为唯一的连接,并且drop database YourDatabaseName仍将删除文件。

ivan_pozdeev 回复 38分钟 前

@Keith 在脚本中,你没有连接到数据库,所以它不是“你的连接”,而是会留下一些其他的。紧接着set offline, 你可以发出set online以避免剩余文件问题(是的,存在竞争条件的可能性)。

eythort 回复 38分钟 前

谢谢!我没有意识到之前在该数据库上执行的 SQL Management Studio 中的一些带有 sql 语句的选项卡导致我的数据库在使用中被报告。使用大师,然后去,让一切正常!

0
Pourya 回答 38分钟 前

您可以通过执行以下操作获取 SSMS 提供的脚本:

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

该脚本将如下所示:

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [YourDatabaseName]
GO
Saurabh Sinha 回复 38分钟 前

我不建议任何用户在单用户模式下使用数据库,因为这可能会导致您失去与某些应用程序用户的当前连接,并且不必要的麻烦来查找这些用户并杀死相同的用户,或者有时如果与 db 的连接是,您必须重新启动 sql server如此频繁。

0
Sodoshi 回答 38分钟 前

鲜为人知:GO sql 语句可以取整数为重复上一条命令的次数。
所以如果你:

ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO

然后:

USE [DATABASENAME]
GO 2000

这将重复 USE 命令 2000 次,在所有其他连接上强制死锁,并获得单个连接的所有权。 (让您的查询窗口可以按照您的意愿进行操作。)

diceless 回复 38分钟 前

GO 不是 TSQL 命令,而是仅由 sqlcmd 和 osql 实用程序和 SSMS 识别的特殊命令。

0
Sacha 回答 38分钟 前

根据我的经验,在大多数情况下使用 SINGLE_USER 会有所帮助,但是,应该小心:我经历过在我开始 SINGLE_USER 命令和完成它之间的情况……显然另一个“用户”得到了SINGLE_USER 访问,而不是我。如果发生这种情况,您将面临一项艰巨的工作,试图恢复对数据库的访问(在我的情况下,它是为具有 SQL 数据库的软件运行的特定服务,在我之前获得了 SINGLE_USER 访问)。我认为应该是最可靠的方法(不能保证,但这是我将在未来几天测试的方法),实际上是:
– 停止可能干扰您访问的服务(如果有)
– 使用上面的“kill”脚本关闭所有连接
– 之后立即将数据库设置为 single_user
– 然后进行恢复

Ross Presser 回复 38分钟 前

如果 SINGLE_USER 命令与您的(脚本化的)恢复命令在同一个批次中——没有被 GO 语句分隔! – 然后,根据我的经验,没有其他进程可以获取单用户访问权限。然而,我今晚被抓住了,因为我每晚安排的 set-single-user;restore;set-multi-user 任务爆炸了。另一个进程对我的 bak 文件具有独占文件访问权限(smh)因此恢复失败,然后是 SET MULTI_USER 失败……这意味着当我在半夜被叫来清理血液时,其他人拥有 SINGLE_USER 访问权限并且不得不被杀死。

0
Chris Bates 回答 38分钟 前

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;