locked
Mirroring database in recovery state after server got rebooted RRS feed

  • Question

  • Hi there,

     

    We have a Database mirroring environment set up using sql server 2005 Enterprise, no witness, safety OFF, asynchronous mode.  The principal server has 2 databases, both being setup with mirroring and sharing the same mirroring server.  When the principal and mirroring servers get reboot after patching activities, the sql admin team wasn't notified to prep for db shutdown, one of the 2 databases were able to resume its mirroring session, but the other database on principal server was stuck in RESTORING state.  We had to stop sql services on the primary, the stop sql services on the secondary server, and then restart services on the primary then secondary to get the troubled mirroring databases on primary server to recover.  Anyone have any ideas why it was stuck in restoring state before that?  This happened consistently for several times whenever the servers got rebooted.  Any tips is greatly appreciated!  Thanks!

    Saturday, July 12, 2008 1:04 AM

All replies

  • You don't need to do anything for mirroring. Once rebooted and the server is up mirroring will automatically be established. What is the size of the database ? Were there any huge transactions going on while the server was rebooted ? Was it in 'Restoring' state or in 'Recovering' state ? Can you please confirm this. We haven't faced this issue till now..

    - Deepak
    Saturday, July 12, 2008 8:20 AM
  • Hi.  I have exactly the same issue.  Version = 9.00.1399. 

     

    After the SQL*Server restart (Services only, not a reboot of the server) all but two of the user databases which are mirrored from a principal have all gone IN RECOVERY.  This is the second time this has happened on 9.00.1399.  I believe this is an RTM issue as I have not witnessed this behaviour on any SP1\2-3239, 3257 implementation.  The fix is to apply at least SP1, although I would recommend that SP2 with 3257 (Post SP2 Rollup #8) be applied.  I have witnessed significant performance increase for computation queries, including nested queries and cursors (sorry, I know it's the 'C' word but 'horses for courses' and all that!).

     

    There is no recovery available for the databases in this state that i can find with RTM (9.00.1399).  The solution is to drop the mirror, apply the SP's\HF's and re-create the mirror.  Nasty.  However, once done you'll hopefully never have to experience this flaw again.  Admittedly this is why Service Packs and fixes are released. Surprise)

     

    I would recommend that all SQL*Server customers apply SP2 if not applied already.  If AWE is enabled you will need to apply 9.00.3239 (Post SP2 Rollup #7).  9.00.3257 was released on the 15th July and I would apply that too for the reason stated above.

     

    I hope this helps.

     

    All the best,

     

    Matt.

    Thursday, July 31, 2008 8:09 PM
  • Did you verified Number of VLF's and size of each VLF on ur Principal Database?

    How big is your LDF file? 


    Sreekanth Bandarla MCITP - SQL Server 2008 Database Administrator.
    Wednesday, July 27, 2011 3:22 AM
  • Matt,  SQL Server 2005 has SP4 to be installed
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 27, 2011 5:11 AM
  • I know this phenomenon too, but normaly this has nothing to do with service pack levels, but everything with the way you have your database growth set.

    Per default the transaction logfile starts with 1 MB and grows by 10%, which means that by the time it reaches 1 GB it has multiple thousands of extends. At startup/recovery every single one of those needs to be processed individually, leaving the recovery for quite a while. The only way around it is to shrink the LDF and regrow it with steps that make more sense...

    Lucifer

    Thursday, July 28, 2011 6:14 AM