SQL SERVER – Msg 3743: The Database is Enabled for Database Mirroring. Database Mirroring Must be Removed Before you Drop the Database

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.

SQL SERVER - Msg 3743: The Database is Enabled for Database Mirroring. Database Mirroring Must be Removed Before you Drop the Database drop-mirror-err-01

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.

SQL SERVER - Msg 3743: The Database is Enabled for Database Mirroring. Database Mirroring Must be Removed Before you Drop the Database drop-mirror-err-02

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)

SQL Error Messages, SQL Mirroring, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Backup to URL error: Operating system error 50(The request is not supported.)
Next Post
SQL SERVER – Unable to Launch SSMS Error – Cannot Find One or More Components. Please Reinstall the Application

Related Posts

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.

    Reply
  • very similar to problems with transactional replication where the partner is gone…

    Reply
  • Marcelo Lucas Guimarães
    September 24, 2019 4:46 pm

    Just what I was needing.
    Thank you!

    Reply

Leave a Reply