I often go to do various seminars and presentations at various organizations.
During presentations I often create and drop various databases for demonstrations purpose. Recently in one of the presentations, I tried to remove my recently created database, I got following error.
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 option that I should go and find open session and close it right away; later followed by dropping the database. As I was in rush I quickly wrote down following code and I was able to successfully drop the database.
ALTER DATABASE MyDBName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE MyDBName
Please note that I am doing all this on my demonstrations, do not run above code on production without proper approvals and supervisions.
Reference: Pinal Dave (http://blog.SQLAuthority.com)