latence in log shipping

  • Hello everyone

    I have a SQL instance configured in logshiping  mode
    I receive alerts on latencies of log file restoration
    The secondary ProductionSQLA \ CAPHN. log shipping database has a recovery threshold of 45 minutes and is not synchronized. No restoration has been done for 49 minutes. The latency of the restoration is 5 minutes. Check the agent log information and the log shipping monitor

    After x time I had a total stop logshiping 
     

    Erreur : 14421, Gravité : 16, État : 1.

     

    The log shipping secondary database Prod_FIDELISATION has restore threshold of 45 minutes and is out of sync. No restore was performed for 620 minutes. Restored latency is 40 minutes. Check agent log and logshipping monitor information.

    Who has an idea about the root cause of this slowness and how I can improve perfermence?

    Thanks

  • joujousagem2006 1602 - Monday, April 2, 2018 7:03 AM

    Hello everyone

    I have a SQL instance configured in logshiping  mode
    I receive alerts on latencies of log file restoration
    The secondary ProductionSQLA \ CAPHN. log shipping database has a recovery threshold of 45 minutes and is not synchronized. No restoration has been done for 49 minutes. The latency of the restoration is 5 minutes. Check the agent log information and the log shipping monitor

    After x time I had a total stop logshiping 
     

    Erreur : 14421, Gravité : 16, État : 1.

     

    The log shipping secondary database Prod_FIDELISATION has restore threshold of 45 minutes and is out of sync. No restore was performed for 620 minutes. Restored latency is 40 minutes. Check agent log and logshipping monitor information.

    Who has an idea about the root cause of this slowness and how I can improve perfermence?

    Thanks

    Did you just setup log shipping and experiencing latency or log shipping was setup a while back?

    Alex S
  • Check the backup, copy and restore log shipping jobs for the frequency as well as any errors.
    You would also want to look at the history and error tables for log shipping in msdb. Tables are: dbo.log_shipping_monitor_history_detail and dbo.log_shipping_monitor_error_detail

    Sue

  • joujousagem2006 1602 - Monday, April 2, 2018 7:03 AM

    Hello everyone

    I have a SQL instance configured in logshiping  mode
    I receive alerts on latencies of log file restoration
    The secondary ProductionSQLA \ CAPHN. log shipping database has a recovery threshold of 45 minutes and is not synchronized. No restoration has been done for 49 minutes. The latency of the restoration is 5 minutes. Check the agent log information and the log shipping monitor

    After x time I had a total stop logshiping 
     

    Erreur : 14421, Gravité : 16, État : 1.

     

    The log shipping secondary database Prod_FIDELISATION has restore threshold of 45 minutes and is out of sync. No restore was performed for 620 minutes. Restored latency is 40 minutes. Check agent log and logshipping monitor information.

    Who has an idea about the root cause of this slowness and how I can improve perfermence?

    Thanks

    What threshold settings did you use when creating the plan, did you accept the defaults?
    What are the specified restore times for your restore job on your secondary?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • 1. Check if there are 2 log backup jobs created in the primary one default and other by logshipping. When configuring logshipping, you need to stop the default log backup jo. Else logshipping

    copy job only copies the log files taken by logshipping process and due to lsn mismatch restore would not be successful on secondary

    2. Check the restore job in the secondary instance. If it fails, check the details and you would get an answer. But if it shows successful, then query msdb.dbo.log_shipping_monitor_secondary

    and find out last file copied and restored. At times this shows NULL which means there is no file to copy / restore.

    3. Check for any missing folder permissions for copy of files to happen.

Viewing 5 posts - 1 through 4 (of 4 total)

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