如何在 sql 脚本中指定“关闭现有连接”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1711840/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I specify "close existing connections" in sql script
提问by nick
I'm doing active development on my schema in SQL Server 2008 and frequently want to rerun my drop/create database script. When I run
我正在 SQL Server 2008 中对我的架构进行积极的开发，并且经常想要重新运行我的删除/创建数据库脚本。当我跑
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase')
DROP DATABASE [MyDatabase]
GO
I often get this error
我经常收到这个错误
Msg 3702, Level 16, State 4, Line 3
Cannot drop database "MyDatabase" because it is currently in use.
If you right click on the database in the object explorer pane and select the Delete task from the context menu, there is a checkbox which to "close existing connections"
如果您右键单击对象资源管理器窗格中的数据库并从上下文菜单中选择删除任务，则会有一个“关闭现有连接”复选框
Is there a way to specify this option in my script?
有没有办法在我的脚本中指定这个选项？
回答by Andomar
You can disconnect everyone and roll back their transactions with:
您可以断开每个人的连接并通过以下方式回滚他们的交易：
alter database [MyDatbase] set single_user with rollback immediate
After that, you can safely drop the database :)
之后，您可以安全地删除数据库:)
回答by hgmnz
Go to management studio and do everything you describe, only instead of clicking OK, click on Script. It will show the code it will run which you can then incorporate in your scripts.
转到管理工作室并执行您描述的所有操作，只需单击“脚本”而不是单击“确定”。它将显示它将运行的代码，然后您可以将其合并到您的脚本中。
In this case, you want:
在这种情况下，您需要：
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
回答by AlexD
According to the ALTER DATABASE SETdocumentation, there is still a possibility that after setting a database to SINGLE_USER mode you won't be able to access that database:
根据ALTER DATABASE SET文档，在将数据库设置为 SINGLE_USER 模式后，您仍然有可能无法访问该数据库：
Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode.
在将数据库设置为 SINGLE_USER 之前，请确认 AUTO_UPDATE_STATISTICS_ASYNC 选项设置为 OFF。设置为 ON 时，用于更新统计信息的后台线程会与数据库建立连接，您将无法在单用户模式下访问数据库。
So, a complete script to drop the database with existing connections may look like this:
因此，删除具有现有连接的数据库的完整脚本可能如下所示：
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]
回答by Abhishek Upadhyay
I know it's too late but may be its helps some one. on using this take your database offline
我知道为时已晚，但可能对某些人有所帮助。使用它使您的数据库脱机
ALTER DATABASE dbname SET OFFLINE
回答by Deiwys
I tryed what hgmnz saids on SQL Server 2012.
我在 SQL Server 2012 上尝试了 hgmnz 所说的内容。
Management created to me:
管理层创建给我：
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
回答by Shailesh Tiwari
try this C# code to drop your database
试试这个 C# 代码来删除你的数据库
public static void DropDatabases(string dataBase) {
公共静态无效 DropDatabases（字符串数据库）{
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();
}
}
}
