Drop database-single_user, rollback immediate, sp_delete_database_backuphistory

Sometimes, DROP DATABASE action will fail because there are other connections existing for the database to remove. In this case, usually we need to close these connectios firstly.

 

The below statement can be used to close the connections.

 

ALTER DATABASE dbtestname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

 

1. Alter database to be SINGLE_USER mode.

2. The WITH which is WITH ROLLBACK IMMEDIATE will rollback all existing transacions related to this database immediately. Of course, this also close all other connections of this database immediately.

 

Now, we can use DROP DATABASE dbtestname to drop the database.

 

By the way, the store procedure sp_delete_database_backuphistory can be executed before drop the database. It will delete all related backup information of this database.

 

The impacted tables as follows.

backupfile, backupfilegroup, backupmediafamily, backupmediaset, backupset, restorefile, restorefilegroup, restorehistory.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28669558/viewspace-754963/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28669558/viewspace-754963/