One of my clients contacted me for quick assistance. They had Database Mirroring configured for a database. Due to some unknown issue their database mirroring went for a toss. Their end goal was to drop the database so that they can configure database mirroring again. They already had a backup of the database already taken before the issue. So far things were looking simple and all they wanted was to drop the database.
They tried to use SQL Server Management Studio to drop the database and it failed with the error mentioned in the blog title.
When I tried DROP DATABASE command in T-SQL then we got below error in SSMS.
Msg 3743, Level 16, State 1, Line 1
The database ‘MyBadDatabase’ is enabled for database mirroring. Database mirroring must be removed before you drop the database.
This is the same message which we are seeing in SSMS also. As the error message says, disable database mirroring buy the challenge was that the “Properties” option was grayed out as shown below.
WORKAROUND/SOLUTION
I tried various options to recover the database do delete it. None of them worked. The command which worked for me was
ALTER DATABASE MyBadDatabase SET PARTNER OFF
Once the command was run, it was in Restoring state. Now, I had option to recover the database or drop the database. As I mentioned above, they end goal was to drop the database, so we went ahead and dropped it.
Let me know what you think of this error, if you have faced this error before, now you know how to solve it. Meanwhile, if your SQL Server is running slow, you can reach out to me by clicking here Comprehensive Database Performance Health Check and I will be happy to help you to fix your SQL Server’s performance.
Reference: Pinal Dave (https://blog.sqlauthority.com)
