SQL Server: Various options to Drop a user Database

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

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

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.