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.
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.
Reference: Pinal Dave (
http://blog.SQLAuthority.com
)
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
Pingback: SQL SERVER – Weekly Series – Memory Lane – #003 « SQL Server Journey with SQL Authority
muchas gracias
Thanks brother.. :)