Close Current Connection to a Database Using T-SQL

If you delete a database object using MS SQL2008 Server Management Studio, you'll be prompted with options as shown below:

By selecting these check boxes, MS SQL2008 Server Management Studio deletes the database and ensures the backup and restore history for the subject database to be deleted from the MSDB database, as well as terminates connections to the subject database.

But, if you use T-SQL to delete a database (as shown below), sometimes you'll get error message "Cannot drop database "DATABASE_NAME" because it is currently in use. (.Net SqlClient Data Provider)".

USE [master]
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DATABASE_NAME')
BEGIN
    DROP DATABASE [DATABASE_NAME]
END
GO

This is because the selected database currently used by another database user.

So the question here is how to write T-SQL that able to delete database and break the existing connection to the database? The following T-SQL show you how to do that:

USE [master]
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N' DATABASE_NAME')
BEGIN
    EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N' DATABASE_NAME'
    ALTER DATABASE DATABASE_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE DATABASE_NAME SET SINGLE_USER
    DROP DATABASE [DATABASE_NAME]
END
GO

Note:

• "ALTER DATABASE < DATABASE_NAME > SET SINGLE_USER" will allow current connection to finish their business and commit their work.
• "ALTER DATABASE < DATABASE_NAME > SET SINGLE_USER WITH ROLLBACK_IMMEDIATE" option immediately disconnects all open connections and rolls back any changes from unfinished transactions.