如何在 SQL 脚本中指定“关闭现有连接”？

Question

我在 SQL Server 2008 上进行架构的积极开发，经常需要重新运行我的删除/创建数据库脚本。当我运行

USE [master]
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase')
DROP DATABASE [MyDatabase]
GO

我经常遇到这个错误

Msg 3702, Level 16, State 4, Line 3
Cannot drop database "MyDatabase" because it is currently in use.

如果您在对象资源管理器窗格中右键单击数据库，并从上下文菜单中选择删除任务，则会出现一个复选框，用于“关闭现有连接”

是否有一种方法可以在我的脚本中指定此选项？

- nick

Answer 1

您可以使用以下代码断开所有连接并回滚其事务：

alter database [MyDatbase] set single_user with rollback immediate

之后，你可以放心地删除数据库 :)

- Andomar

---

我用过这个，但经常想知道是否存在其他用户进入“单用户”模式的机会 - 这可能吗？还有一种可能的替代方案是 ALTER DATABASE [MyDatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE。 - Kristen

在 single_user 模式下，用户是指你；除非在设置单用户模式后断开连接，否则只有一个其他用户可以登录。 - Andomar

@Kristen，使用您的方法后，我发现SQL Server没有删除MDF和LDF文件。对我来说，设置single_user很好用（我需要不断重新创建数据库）。 - 2xMax

@AndyM：数据库在还原后，将处于与备份所在数据库相同的状态；例如备份处于单用户模式的数据库：还原后的数据库也将处于单用户模式。被还原覆盖的数据库的状态不会产生影响。（虽然这是旧评论，但我想为以后可能阅读此评论的其他人澄清一下） - Kristen

使用这个例子后，我无法再自己进入数据库了。最后在我的脚本末尾将其设置回多用户模式以修复这个问题。 - Jowen

Answer 2

打开管理工具，按照您所描述的做法，只是不要点击“确定”，而是点击“脚本”按钮。它将显示将要运行的代码，然后您可以将其合并到您的脚本中。

在这种情况下，您需要：

ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

- hgmnz

---

我本来想尝试通过按照您所描述的方式（脚本化“删除数据库”对话框）来回答这个问题，但是如果您勾选了“关闭现有连接”复选框，则它不会将ALTER DATABASE行添加到脚本中。 - Matt Hamilton

从向导生成的脚本中，已经为我包含了“alter database”行。 - nick

奇怪。使用哪个管理工具版本？我在2008 x64上。 - Matt Hamilton

我也是： Microsoft SQL Server Management Studio 10.0.1600.22 操作系统 6.0.6001 - nick

我遇到了与ALTER DATABASE未被添加到脚本的相同问题。为了让它被添加到脚本中，我需要确保在生成脚本时，有一个进程（活动连接）正在该数据库上运行。 - Gilbert

Answer 3

根据ALTER DATABASE SET文档，即使将数据库设置为 SINGLE_USER 模式，仍有可能无法访问该数据库:

在将数据库设置为 SINGLE_USER 模式之前，请验证 AUTO_UPDATE_STATISTICS_ASYNC 选项是否已设置为 OFF。当设置为 ON 时，用于更新统计信息的后台线程会对数据库进行连接，并且您将无法在单用户模式下访问数据库。

因此，一个完整的脚本以删除具有现有连接的数据库可能如下所示：

DECLARE @dbId int
DECLARE @isStatAsyncOn bit
DECLARE @jobId int
DECLARE @sqlString nvarchar(500)

SELECT @dbId = database_id,
       @isStatAsyncOn = is_auto_update_stats_async_on
FROM sys.databases
WHERE name = 'db_name'

IF @isStatAsyncOn = 1
BEGIN
    ALTER DATABASE [db_name] SET  AUTO_UPDATE_STATISTICS_ASYNC OFF

    -- kill running jobs
    DECLARE jobsCursor CURSOR FOR
    SELECT job_id
    FROM sys.dm_exec_background_job_queue
    WHERE database_id = @dbId

    OPEN jobsCursor

    FETCH NEXT FROM jobsCursor INTO @jobId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        set @sqlString = 'KILL STATS JOB ' + STR(@jobId)
        EXECUTE sp_executesql @sqlString
        FETCH NEXT FROM jobsCursor INTO @jobId
    END

    CLOSE jobsCursor
    DEALLOCATE jobsCursor
END

ALTER DATABASE [db_name] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [db_name]

- AlexD

Answer 4

我知道现在已经很晚了，但是这可能对一些人有所帮助。关于使用这个，请将你的数据库脱机。

ALTER DATABASE dbname SET OFFLINE

- Abhishek Upadhyay

---

注意：在下线数据库后，“drop offline database”命令不会删除Mdf文件！ - bob217

在运行此答案的OFFLINE命令后立即运行ALTER DATABASE dbname SET ONLINE。SQL服务器仅会删除在线数据库的关联文件（mdf、ldf）。通过连续运行离线和在线语句，所有现有连接将被断开，并且SQL将正确删除所有数据库文件。 - Chris

Answer 5

我尝试了hgmnz在SQL Server 2012上的建议。

管理层为我创建了：

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyDataBase'
GO
USE [master]
GO
/****** Object:  Database [MyDataBase]    Script Date: 09/09/2014 15:58:46 ******/
DROP DATABASE [MyDataBase]
GO

- Deiwys

---

这不会关闭活动连接。 - Jens

请确保您已勾选“关闭现有连接”；如果您不这样做，则将包括“ROLLBACK IMMEDIATE”语句。sp_delete_database_backuphistory是来自于勾选“删除数据库的备份和还原历史信息”。 - Christian.K

如果没有当前连接需要关闭，勾选“关闭现有连接”不会生成ALTER DATABASE SET SINGLE_USER ...。 - ahwm

Answer 6

如果您正在尝试从应用程序中删除它，则连接字符串的初始目录必须为“master”

- ali fuat

---

请添加更多细节以扩展您的答案，例如工作代码或文档引用。 - Community

Answer 7

请看下面。不要点击“确定”，而是按Ctrl + Shift + N，一个新窗口将为您准备好脚本。

- VivekDev

Answer 8

尝试使用以下C#代码删除数据库：

public static void DropDatabases(string dataBase) {

        string sql =  "ALTER DATABASE "  + dataBase + "SET SINGLE_USER WITH ROLLBACK IMMEDIATE" ;

        using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["DBRestore"].ConnectionString))
        {
            connection.Open();
            using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(sql, connection))
            {
                command.CommandType = CommandType.Text;
                command.CommandTimeout = 7200;
                command.ExecuteNonQuery();
            }
            sql = "DROP DATABASE " + dataBase;
            using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(sql, connection))
            {
                command.CommandType = CommandType.Text;
                command.CommandTimeout = 7200;
                command.ExecuteNonQuery();
            }
        }
    }

- Shailesh Tiwari