locked
Unsent Log and Unrestored Log and how SQL Server works on getting caught up RRS feed

  • Question

  • Currently, the principal and mirroring are trying to get caught up because of an error in our network. I'm monitoring the Database Mirroring History GUI and what I see is the Principal and Mirror taking turns on working on the transactions. At this time, the mirror is restoring and the principal is not sending over any transactions, subsequently, the log usage on the principal is growing. Fortunately, I have enough disk space to accommodate what is happening. What I would like to know is if there is a way to manually tell Mirroring to change sides and start sending transactions from the principal to the mirror again. It appears that it waits for the Unrestored Log to get totally caught up before it will start sending transactions from the principal again. Thanks in advance for the assistance.
    Friday, December 11, 2015 3:29 PM

Answers

  • If your mirror cannot catch up, break the mirror, take a differential backup or a series of log backups, restore it/them on the mirror and re-enable it back. In high safety/synchronous mode, the mirror will try to catch up as fast as it can but the principal will not be able to truncate the virtual log files unless they have been persisted and REDO done on the mirror.

    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Saturday, December 12, 2015 6:22 AM

All replies

  • If your Mirroring mode is configured as  High Availability mode (Full safety),  with witness it will  initiate an automatic failover when your Principal goes down.

    if the safety level is  OFF,Manually Fail over the Mirror and then restore it again 

    ALTER DATABASE 'DBNAME' SET PARTNER OFF
    RESTORE DATABASE DBNAME WITH RECOVERY

    In High-Performance mode when Principal goes down. 

    ALTER DATABASE 'DBNAME' SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    read following article

    http://sql-articles.com/articles/high-availability/how-can-i-bring-mirror-database-online-after-principal-server-is-down/
     

    Saturday, December 12, 2015 4:37 AM
  • If your mirror cannot catch up, break the mirror, take a differential backup or a series of log backups, restore it/them on the mirror and re-enable it back. In high safety/synchronous mode, the mirror will try to catch up as fast as it can but the principal will not be able to truncate the virtual log files unless they have been persisted and REDO done on the mirror.

    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Saturday, December 12, 2015 6:22 AM
  • I agree to what Edwin said but before breaking pause mirroring. Wait for a minute and then do as suggested.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Saturday, December 12, 2015 9:31 AM