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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
13 Comments. Leave new
Finally! A quick fix for a problem I face a lot! Thanks!
Well, it depends. It might not be that quick. :) It all depends on how fast the transactions can be rolled back. (Imagine running the code on a production server right in the moment when someone was trying to delete 1 million rows… :) )
Hi sir,
I have one doubt regarding this script.
what happend if this query executes in my system ?
we want to drop the database, then why should we use the ALTER statement ?
can you please explain this query in detail.
Thanks
If you want to drop a database, use
drop database db_name
The reason to Alter database in Single user mode, is to disconnect other connections which use the database you want to drop.
once you set that to single user mode, every other connection gets removed. then you can drop the database.
I agree with Feodor, if some has performed a delete, the drop of the database would depend on how quick the delete operation finishes.
Also Pinal, what was your impressions on PASS Summit this time….
Thank you
muchas gracias
Thanks brother.. :)
Thanks, it works for me.
My pleasure @Vikrant
Its worked for mee..
Is not working for me, I was deleting some registries from one table and when I deleted all the registries from a year in a table, the database puts in recovery mode, and I cannot put database outside that condition
Recovery need to finish before you access database.