” 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:
Drop a database using SQL Server Management Studio by selecting an option like “Close existing connections.”
Drop a database using T-SQL Script
Drop a database using Powershell
Set SINGLE User mode and drop a database
ALTER DATABASE dbrnd SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE dbrnd
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.
ALTER DATABASE dbrnd SET OFFLINE WITH ROLLBACK IMMEDIATE
DROP DATABASE dbrnd
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.
EXEC sp_detach_db 'dbrnd', 'true';
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.