locked
Unable to drop mirror database even even after removing mirror RRS feed

  • Question

  • Hi, I removed the mirroring by running alter database xxx set partner off on the principal server, which removed the mirroring, but I see can't delete the database xxx which was mirror. before

    I get the below error:

    The database 'XXX' is enabled for database mirroringDatabase mirroring must be removed before you drop the database. (Microsoft SQL Server, Error: 3743). Does anyone know how I can drop the database? 

    please advise

    thanks

    farhan


    • Edited by AltafBF Tuesday, April 17, 2018 3:56 PM
    Tuesday, April 17, 2018 3:39 PM

Answers

  • Sorry, not "Alter database xxxx set multi_user", but rather - RESTORE DATABASE XXX WITH RECOVERY.
    • Marked as answer by AltafBF Tuesday, April 17, 2018 4:31 PM
    Tuesday, April 17, 2018 4:28 PM

All replies

  • Firstly, you will need to remove the mirroring by going to the properties of the database on the primary instance, then highlight the mirroring option on the left, then click remove mirroring, then OK.
    Tuesday, April 17, 2018 3:49 PM
  • Sorry I should have mentioned I removed the mirroring too.
    Tuesday, April 17, 2018 3:53 PM
  • So, are you saying you are seeing this error message while trying to drop the database from the secondary after removing mirroring?
    Tuesday, April 17, 2018 3:56 PM
  • that's correct.
    Tuesday, April 17, 2018 3:57 PM
  • I have never had an issue after mirroring was removed, dropping the secondary database.  What's the status now of the secondary database? 
    Tuesday, April 17, 2018 4:12 PM
  • thanks. It says in recovery. data/log files still exist where they are supposed to be.

    Yes it weird. No idea where to go. Server has also been rebooted. Can't take it offline, rename or put it in emergency mode.

    Tuesday, April 17, 2018 4:18 PM
  • Try getting it into a multi-user mode and then re-try deleting it - using - Restore database xxxx with recovery.
    Tuesday, April 17, 2018 4:22 PM
  • Tried it. alter database xxx  set multi_user  

    Msg 5052, Level 16, State 1, Line 1

    ALTER DATABASE is not permitted while a database is in the Restoring state.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.

    Tuesday, April 17, 2018 4:26 PM
  • Sorry, not "Alter database xxxx set multi_user", but rather - RESTORE DATABASE XXX WITH RECOVERY.
    • Marked as answer by AltafBF Tuesday, April 17, 2018 4:31 PM
    Tuesday, April 17, 2018 4:28 PM
  • I tried it this morning it didn't work. A bit later I saw the database status in recovery.

    Just a few minutes ago, the status changed to restoring, and RESTORE DATABASE XXX WITH RECOVERY recovered it.

    So it looks the database might have been running crash recovery for a very long time. That's a guess.

    But thank you for your help. cheers


    • Marked as answer by AltafBF Tuesday, April 17, 2018 4:31 PM
    • Unmarked as answer by AltafBF Tuesday, April 17, 2018 4:31 PM
    Tuesday, April 17, 2018 4:31 PM