SQL Server: Various options to Drop a user Database

” Error – 3702 Drop failed for Database ‘dbrnd’. Cannot drop database “dbrnd” because it is currently in use. SQL Server Drop Database

I am sure that most of the SQL Server professionals faced above error while dropping a database in SQL Server. There are various ways to drop a database in SQL Server.

You have multiple options to drop a SQL Server Database by closing existing connections.

You cannot execute DROP DATABASE in following situations:

  • The database is currently in use
  • If replicated, the database published
  • A database snapshot exists on the database
  • The database is being mirrored
  • The database is suspect
  • The database is a system database

Following are options to drop a database:

Option #1:
Drop a database using SQL Server Management Studio by selecting an option like “Close existing connections.”

Option #2:
Drop a database using T-SQL Script

Option #3:
Drop a database using Powershell

Option #4:
Set SINGLE User mode and drop a database

Option #5:
First take a database offline and drop it
Once you take a database offline, it does not drop physical files of database like .mdf and .ldf. You can use Windows Explorer and can drop .mdf and .ldf files.

Option #6:
First SET SINGLE_USER mode and detach the database
Once you detach the database, it does not drop physical files of database like .mdf and .ldf. You can use Windows Explorer and can drop .mdf and .ldf files.
If you do not delete those file, in future again, you can attach your database.


Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz