I often go to do various seminars and presentations at various organizations. During presentations I often create and drop various databases for the demonstration’s purpose. Recently in one of the presentations, I tried to remove my recently created database, I got following error 3702 which is related to user cannot drop database.
Msg 3702, Level 16, State 3, Line 1
Cannot drop database “MyDBName” because it is currently in use.
The reason was very simple as my database was in use by another session or window. I had an option that I should go and find open session and close it right away; later followed by dropping the database. As I was in a rush I quickly wrote down following code and I was able to successfully drop the database.
USE MASTER GO ALTER DATABASE MyDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE MyDBName GO
Please note that I am doing all this on my demonstrations, do not run above code on production without proper approvals and supervisions. If you drop your database and you have no backup, there is good chance that you will be in big trouble. I strongly suggest that before doing anything on any database, one should take FULL DATABASE BACKUP.
Reference: Pinal Dave (http://blog.SQLAuthority.com)