none
Database Mirroring Unsent Log RRS feed

  • Question

  • Hi,

    We have a 2 node cluster, running Window 2003 R2 SP2 and SQL Server 2005 SP3 Enterprise Edition (x64).

    At 23:15 on 27/05/2012 (GMT), we had a 10 second mirroring timeout.  Since then, the 'unsent log' has never gone below 112KB.  The unsent log goes up quite happily as transactions are processed on the principal, as does the send rate.  But, once it appears to go idle again, it doesn't seem to be able to clear this mysterious 112KB that's stuck on the principal.  Also, when the unsent log is idling at 112KB, the Time To Send value is set to infinite.  Prior to the mirroring timeout, the unsent log always went down to 0KB.

    The only error in the SQL Server error log is for the mirroring timeout.

    Any ideas?



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you


    Wednesday, May 30, 2012 1:33 PM

Answers

  • I have seen something like this once on SQL 2008. What I did was to restart the endpoint on both sides (ALTER ENDPOINT <name> STATE=STOPPED and then STATE=STARTED) and then it cleared up. Couldn't repro it though...

    Thursday, May 31, 2012 10:45 AM

All replies

  • Hi Andrew Bainbridge,

    >> once it appears to go idle again, it doesn't seem to be able to clear this mysterious 112KB that's stuck on the principal.

    Regarding to your description, after timeout, the unsent log is waiting in the send queue meanwhile there might be new active transactions. That would take longer to make unsent log down.

    >> when the unsent log is idling at 112KB, the Time To Send value is set to infinite.

    Could you check the Current rate of new transactions and current send rate and Unsent log /Unrestored Log?  If the current send rete is 0kb/sec. then the time to send value is infinite as normal. If the unsent log is 112 KB and restoring rate is 0, means there is problem where it is not able to restore might be related to network issue, and then you can try to take a differential backup of principal and restored on mirrored after removing mirroring.

    For how to check the rate as above please follow the steps to check first: Right click > Task>Launch Database mirroring monitor>Select registered database (If not register then register it)>Check the Status tab > then next column called "Principal Log “&’Mirrored Log'.

    Last you can monitor the database mirroring, for more information please refer to
    1. Possible Failures During Database Mirroring http://msdn.microsoft.com/en-us/library/ms190913.aspx
    2. Monitoring Database Mirroring (SQL Server) http://msdn.microsoft.com/en-us/library/ms190030.aspx


    Regards,
    Amber zhang
    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Thursday, May 31, 2012 4:47 AM
    Moderator
  • Hi Amber,

    Thank you for responding.

    The mirroring timeout I referred to only happened once, at 23:15.  There were no subsequent timeouts.  But nearly 4 days later, we still have this 112KB stuck in the log on the principal.  I don't think resources, etc. can be an issue, as during trading, we see hundreds of MB's mirror across to DR in seconds.

    Using Idera Diagnostic Manager, and the Database Mirroring Monitor, I can see the following:

    Principal Log:

    Unsent Log: 112KB

    Oldest Unsent Transaction: 00:00:00

    Time to send log (estimated): infinite

    Current send rate: 0 KB/sec

    Current rate of new transactions: 0 KB/sec

    Mirror Log:

    Unrestored log: 0KB

    Time to restore log (estimated): 'blank'

    Current restore rate: 0 KB/sec

    When we get new transactions, the time to send log (estimated) goes up to, for example, 10 seconds.  the current send rate goes to, for example, 10MB/Sec and the current rate of new transactions goes up as well.  Once it settles down again and there's nothing being sent by the principal, nothing being received by the mirror, the Unsent Log still says 112KB.

    I have 7 other databases on this server mirroring quite happily to the same DR site.  They share the same disks, network interfaces, etc.

    I suspect I'm going to have to reinitialise mirroring, but was interested in diagnosing this further.

    Thanks again,



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Thursday, May 31, 2012 7:40 AM
  • I have seen something like this once on SQL 2008. What I did was to restart the endpoint on both sides (ALTER ENDPOINT <name> STATE=STOPPED and then STATE=STARTED) and then it cleared up. Couldn't repro it though...

    Thursday, May 31, 2012 10:45 AM
  • interesting.  I'll give this a go on Saturday and let you know.

    Thanks for the tip



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Thursday, May 31, 2012 11:11 AM
  • Andrew,

    It's interesting, I would have thought if there wactually was old unsent log that it would show up somehow... such as  a log_reuse_wait value or finding an orphaned distributed transaction.

    I found a few connect items, the first one seems like what you are seeing and the second I'm not sure but a few people seem to have had it happen.

    http://connect.microsoft.com/SQLServer/feedback/details/675830/spurious-oldest-unsent-transaction-alerts-for-database-mirroring

    http://connect.microsoft.com/SQLServer/feedback/details/555362/mirroring-monitor-shows-unsent-log-in-state-synchronized-with-synchronous-mirroring

    Let me know if you put in a connect item!

    -Sean


    Sean Gallardy, MCC | Blog

    Thursday, May 31, 2012 12:18 PM
    Answerer
  • thanks for the connect links, Sean.  I hadn't seen them.  I can't really tell if the first ones an alerting problem, or like mine, a problem flushing the log.  I've got a bit more flexibility to test stuff on the weekend, so I'll let you know how it goes.


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Thursday, May 31, 2012 1:49 PM
  • I have seen something like this once on SQL 2008. What I did was to restart the endpoint on both sides (ALTER ENDPOINT <name> STATE=STOPPED and then STATE=STARTED) and then it cleared up. Couldn't repro it though...

    this worked a treat.  Restarted the end points on both ends and it cleared the log.  Thanks


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Wednesday, June 6, 2012 8:03 AM