latence in log shipping

• joujousagem2006 1602

  SSCommitted

  Points: 1779

  More actions

  April 2, 2018 at 7:03 am

  #330242

  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

• AlexSQLForums

  SSChampion

  Points: 14277

  More actions

  April 2, 2018 at 11:34 am

  #1985211

  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

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  April 3, 2018 at 11:43 am

  #1985341

  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

• Perry Whittle

  SSC Guru

  Points: 233848

  More actions

  April 4, 2018 at 9:15 am

  #1985482

  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" 😉

• harikumar.mindi

  SSCommitted

  Points: 1972

  More actions

  April 5, 2018 at 4:37 am

  #1985609

  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.