Post reply

Mirroring restore rate very low (< 5 MB/sec) - database mirror stuck in "synchronizing" state for several hrs

  • July 16, 2012 at 3:13 pm

    #277867

    A production mirroring session started synchronizing this morning and at first the restore rate on the mirror server was at about 30 MB /sec.

    Suddenly it dropped to < 5 MB /sec and has stayed like this for most of the day.

    As a result, the mirror has been in "synchronizing" status for most of the day, and we are unable to create snapshots for reporting users.

    What could be the cause of such a low restore rate?

    What should I be looking for?

    Any links/suggestions would be most welcome.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • July 16, 2012 at 3:23 pm

    #1513342

    Just some random thoughts:

    Check the disks for contention

    Check to see if anything else is running on the box and consuming inordinate CPU and/or memory

    Make sure your anti-virus software isn't on and attempting to scan your database files

    check the sql server for blocked processes

    Depending on how far the data is travelling, you might have to have your network folks take a look at the traffic between source and mirror

    And then again, I might be wrong ...
    David Webb

  • July 17, 2012 at 12:45 am

    #1513414

    I think the above all is sufficient to get the culprit

    but mostly It would be Network Bandwidth

    Thanks

    S.R.Kundur

  • July 17, 2012 at 2:41 am

    #1513454

    Thanks both for your suggestions.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • July 17, 2012 at 11:11 am

    #1513733

    There is also a bug related to this (though I don't know if a KB has been published Try stopping and starting the mirroring queues to see if it resolves the problem. If not, continue investigating and consider opening a case with PSS.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • July 18, 2012 at 3:04 am

    #1514081

    Robert Davis (7/17/2012)

    There is also a bug related to this (though I don't know if a KB has been published Try stopping and starting the mirroring queues to see if it resolves the problem. If not, continue investigating and consider opening a case with PSS.

    Thank you for the suggestion.

    Do you mean suspend and resume the mirroring session?

    I will try that next time this happens.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • July 18, 2012 at 9:10 am

    #1514281

    No I meant stop/start the mirroring endpoints. Use an Alter Endpoint caommand and do it on both partners.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • July 18, 2012 at 8:56 pm

    #1514587

    Robert Davis (7/18/2012)

    No I meant stop/start the mirroring endpoints. Use an Alter Endpoint caommand and do it on both partners.

    Thanks for clarifying, now I know what you mean; will try that.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • July 21, 2012 at 9:40 pm

    #1515923

    Could the slow restore rate be caused by high file fragmentation on the mirror server?

    Perhaps we are due for a file defrag outage.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • July 21, 2012 at 10:55 pm

    #1515925

    Marios Philippopoulos (7/21/2012)

    Could the slow restore rate be caused by high file fragmentation on the mirror server?

    Perhaps we are due for a file defrag outage.

    No file level fragmentation would cause a slow degradation of performance, not a sudden one.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • February 18, 2013 at 7:44 am

    #1588855

    Did you ever find a solution to this problem? I'm also experiencing very low restore rates, less than 1MB/s.

    The unrestored log has only grown by 94MB in 8 minutes, but mirroring just isn't keeping up.

    We're running SQL 2012 RTM. We've got a gigabit link between the 2 servers which isn't being touched. Data seems to have been sent to the passive server but its just not applying the transactions to the databases quickly enough.

    Also checked disks and only seeing 9MB/s writing to the disks, and they're capable of much more obviously.

    Darren

  • February 19, 2013 at 8:22 am

    #1589227

    darrenstokes (2/18/2013)

    Also checked disks and only seeing 9MB/s writing to the disks, and they're capable of much more obviously.

    Are they? Right now? How do you know?

    Run a quick benchmark locally and across the purported gigabit link both - SQLIO is my favorite quick tool for that, but a basic file copy will work, too. Do it both ways, just in case.

    Note that when a disk controller loses its cache, or its battery (which almost always then disabled the cache), you can see a sudden dramatic drop in write performance.

    If nothing else, get the HW folks to run online or offline diagnostics on both ends, the end that's being written to first.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply