+- 0Day Forums (https://zeroday.vip)
+-- Forum: Coding (https://zeroday.vip/Forum-Coding)
+--- Forum: Database (https://zeroday.vip/Forum-Database)
+---- Forum: Microsoft SQL Server (https://zeroday.vip/Forum-Microsoft-SQL-Server)
+---- Thread: How do I specify "close existing connections" in sql script (/Thread-How-do-I-specify-quot-close-existing-connections-quot-in-sql-script)
How do I specify "close existing connections" in sql script - garoxzo - 07-31-2023
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
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?
RE: How do I specify "close existing connections" in sql script - making327 - 07-31-2023
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 :)
RE: How do I specify "close existing connections" in sql script - mantle433 - 07-31-2023
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
RE: How do I specify "close existing connections" in sql script - recomprehend603050 - 07-31-2023
According to the [ALTER DATABASE SET][1] documentation, there is still a possibility that after setting a database to SINGLE_USER mode you won't be able to access that database:
> 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.
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]
[1]:
[To see links please register here]
RE: How do I specify "close existing connections" in sql script - tangledycqfncraa - 07-31-2023
I tryed what hgmnz saids on SQL Server 2012.
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
RE: How do I specify "close existing connections" in sql script - loonvwyergpiky - 07-31-2023
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
RE: How do I specify "close existing connections" in sql script - lonajnmojntgzi - 07-31-2023
try this C# code to drop your database
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();
}
}
}
RE: How do I specify "close existing connections" in sql script - evadnee137 - 07-31-2023
if you are trying to drop it from you application, your connection string's initial catalog must be "master"
RE: How do I specify "close existing connections" in sql script - Sirsulla158 - 07-31-2023
See below. Do not click OK, but Ctrl + Shift + N, and a new window with the script will be ready for you.
[![Drop Database Dialog for script generation][1]][1]
[1]: