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)
3 Comments. Leave new
In this post, I understand that system show the message
which describes we need first to remove database mirroring for ‘MyBadDatabase’ after that we must be dropped ‘MyBadDatabase’.
So, as my solution is that remove the configuration for database mirroring and next i will drop this database.
I has been searched some methods for removing database mirroring from microsoft sites.
– Using SQL Server Management Studio
To remove database mirroring
During a database mirroring session, connect to the principal server instance, in Object Explorer, click the server name to expand the server tree.
Expand Databases, and select the database.
Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
In the Select a Page pane, click Mirroring.
To remove mirroring, click Remove Mirroring. A prompt asks for confirmation. If you click Yes, the session is stopped and mirroring is removed from the database.
– Using Transact-SQL
To remove database mirroring, use the Database Properties. use the Mirroring page of the Database Properties dialog box.
To remove database mirroring
Connect to the Database Engine of either mirroring partner.
From the Standard bar, click New Query.
Issue the following Transact-SQL statement:
ALTER DATABASE database_name SET PARTNER OFF
Thanks.
very similar to problems with transactional replication where the partner is gone…
Just what I was needing.
Thank you!