none
Can't drop DB in Disconnected / In Recovery mode

    Question

  •  

    I have 2 SQL Servers running SQL 2005 SP2. DB mirroring was established and was running ok.

     

    Later on the partner server has been powered off without removing the mirroring.

     

    After this the mirroring on the principle server was removed. The db was upgraded to new version and so on.

     

    In 2 weeks the mirrored server was powered on. The mirrored DB are in XXX (Mirror, Disconnected / In recovery) mode.

    I am getting the following message:

     

    The database 'XXX' is enabled for database mirroring. Database mirroring must be removed before you drop the database. (Microsoft SQL Server, Error: 3743)

     

    Does anyone know how I can drop the DBs or remove the mirroring on the partner server for re-enabling a mirroring again.

     

     

    Thanks,

     

    Vlad

     

    Tuesday, October 16, 2007 7:35 PM

Answers

All replies

  • Execute the below command in SSMS,

    Code Block
    ALTER DATABASE <database_name> SET PARTNER OFF

     

     

    It will remove mirroring ! then you can drop the database. Else if you want to remove via GUI pls refer,

    http://msdn2.microsoft.com/en-us/library/ms190471.aspx

     

    Regards

    Deepak

    Tuesday, October 16, 2007 8:29 PM
    Moderator
  • Hi...


    This is HImanshu Kumar Pant from eWebGuru.com, I use the above command, but it is taking endless time. Finally I had to stop this query. I don't think this is universal solution for deleting a database which is in recovery/readonly mode.

    Thanks
    Himanshu Kumar Pant
    eWebGuru.com
    Reliable web hosting
    http://www.ewebguru.com
    Wednesday, December 23, 2009 2:07 PM
  • Hi...


    This is HImanshu Kumar Pant from eWebGuru.com, I use the above command, but it is taking endless time. Finally I had to stop this query. I don't think this is universal solution for deleting a database which is in recovery/readonly mode.

    Thanks
    Himanshu Kumar Pant
    eWebGuru.com
    Reliable web hosting
    http://www.ewebguru.com
    When I have needed to delete a database in recovering mode that has been involved in mirroring, I got the same error listed above by VLAD, I used the same ALTER DATABASE script as posted by Deepak to remove the database as a partner in the mirror (removing mirroring from that database) I was then able to delete the database. Not sure if that helps
    Saturday, March 13, 2010 11:35 AM
  • Greetings,

    I have a SQL Server 2008 R2 instance and a database on which mirroring was removed. As expected it is in a "Disconnected / In Recovery" state.  I removed mirroring and now would like to drop the database.

    However, the "ALTER DATABASE [MyDB] SET PARTNER OFF;" statement returns:

    Database "MyDB" is not configured for database mirroring.

     

    OK - then I attempt a drop statement and it returns:

    The database 'MyDB' is enabled for database mirroring. Database mirroring must be removed before you drop the database.

     

    So the database is "stuck" in this permanent state and the "solution" listed here is useless to me. Any further information would be greatly appreciated (by me and anyone else that has experienced this "orphan" situation).

    Best Regards,

    pk


    Production DBA
    Wednesday, September 21, 2011 1:46 PM
  • Hi Kelsayp (or anyone else in this situation) - i fixed the same scenario by first restoring the broken mirror (RESTORE DATABASE foo WITH RECOVERY), it could then be dropped with no error
    Monday, October 1, 2012 7:18 PM