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