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

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 :)

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

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]

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

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

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

Related

Recent Posts