SQL SERVER – FIX ERROR 3702 Cannot drop database “MyDBName” because it is currently in use

SQL
13 Comments

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

SQL SERVER – FIX ERROR 3702 Cannot drop database “MyDBName” because it is currently in use keep-calm-and-drop-database

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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Reducing CXPACKET Wait Stats for High Transactional Database
Next Post
SQL SERVER – Information Related to DATETIME and DATETIME2

Related Posts

13 Comments. Leave new

  • Finally! A quick fix for a problem I face a lot! Thanks!

    Reply
  • Feodor Georgiev
    November 15, 2010 1:41 pm

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

    Reply
  • 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

    Reply
    • If you want to drop a database, use

      drop database db_name

      Reply
    • 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.

      Reply
  • 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

    Reply
  • muchas gracias

    Reply
  • Thanks brother.. :)

    Reply
  • Vikrant Kanojiya
    February 24, 2015 6:46 pm

    Thanks, it works for me.

    Reply
  • Sudheer Goturi
    July 2, 2015 2:56 pm

    Its worked for mee..

    Reply
  • Lobsan Mendoza
    January 2, 2019 7:06 pm

    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

    Reply

Leave a Reply

Menu