locked
Mirror DB Stuck in (Mirror, Disconnected / In Recovery) state After Breaking the Mirror RRS feed

  • Question

  • After a network outage and a lot of processing our mirroring fell behind for one of our DBs.  It started to catch up, then just stopped shipping logs and on the restoring side it was running very slow.  It was clear that the mirrors were corrupt and needed to be rebuilt.

    As I have run into this problem before, I first tried to break the mirroring on the Mirror DB's SQL Server, by issuing the following commands with the following results (we are using TDE encryption, so the commands are wrapped in commands needed for encryption):

    OPEN MASTER KEY DECRYPTION BY PASSWORD = '<MasterKeyPwd>'
        ALTER DATABASE <DbName> SET PARTNER OFF
    CLOSE MASTER KEY

    Msg 1404, Level 16, State 5, Line 2
    The command failed because the database mirror is busy. Reissue the command later.

    OPEN MASTER KEY DECRYPTION BY PASSWORD = '<MasterKeyPwd>'
        ALTER DATABASE <DbName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
    CLOSE MASTER KEY

    Msg 1404, Level 16, State 5, Line 2
    The command failed because the database mirror is busy. Reissue the command later.

    OPEN MASTER KEY DECRYPTION BY PASSWORD = '<MasterKeyPwd>'
        RESTORE DATABASE <DbName> WITH RECOVERY
    CLOSE MASTER KEY

    Msg 3101, Level 16, State 1, Line 2
    Exclusive access could not be obtained because the database is in use.
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.

    I tried these 3 commands in various orders with no luck, I believe this was because the mirroring was behind.  So I had to kill the mirroring from the Principal DB's SQL Server, which worked.  But the mirror DB was still stuck in the (Mirror, Disconnected / In Recovery) state.  This means you can't restore over the DB to rebuild the mirror.  I tried the 3 commands above again in various orders with no luck.

    The only way I have found is to stop the mirror SQL Server instance, delete all the DATA and LOG files for the DB, and start it back up.  Then you can restore backups off the principal SQL Server instance to start rebuilding your mirror.

    This is a real pain, and we have other DBs mirrored between the same instances, so we are forcing all of those to re-sync.  It is also dangerous in case you delete the wrong DATA / LOG files, it will create even more work for you.

    I should mention we only use 'High performance (asynchronous)' mode on all our mirrors, but I assume that is quite standard.  We are also using TDE Encryption, which is becoming quite standard.

    Wednesday, July 23, 2014 10:06 PM

Answers

  • Have removed mirroring correctly ?
    ALTER DATABASE database_name SET PARTNER OFF 

    then you can do recovery then offline or online or delete and anything ...

    RESTORE DATABASE database_name WITH RECOVERY;


    Raju Rasagounder Sr MSSQL DBA

    • Proposed as answer by Sofiya Li Thursday, July 24, 2014 5:45 AM
    • Marked as answer by Sofiya Li Monday, August 11, 2014 3:06 AM
    Thursday, July 24, 2014 1:32 AM

All replies

  • I should also mention this is for SQL Server 2008 R2 SP2 CU9, however, based on other articles for this plastered over the internet; it doesn't seem like the version or build makes any difference.
    Wednesday, July 23, 2014 10:10 PM
  • Have removed mirroring correctly ?
    ALTER DATABASE database_name SET PARTNER OFF 

    then you can do recovery then offline or online or delete and anything ...

    RESTORE DATABASE database_name WITH RECOVERY;


    Raju Rasagounder Sr MSSQL DBA

    • Proposed as answer by Sofiya Li Thursday, July 24, 2014 5:45 AM
    • Marked as answer by Sofiya Li Monday, August 11, 2014 3:06 AM
    Thursday, July 24, 2014 1:32 AM
  • Also, can you check if you see something interesting in ERRORLOG?


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Thursday, July 24, 2014 6:02 AM