How do I specify close existing connections in sql script

Question

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

User · Answer

According to the ALTER DATABASE SET 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]

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How do I specify "close existing connections" in sql script

Examples related to sql

Examples related to sql-server