SQL SERVER – ERROR – FIX – Msg 3702, Level 16, State 3, Line 1 Cannot drop database “MyDBName” because it is currently in use

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)

9 thoughts on “SQL SERVER – ERROR – FIX – Msg 3702, Level 16, State 3, Line 1 Cannot drop database “MyDBName” because it is currently in use

  1. 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… :) )

    Like

  2. 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

    Like

  3. 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

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #003 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s