Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Log Shipping Message : Skipping log backup file since load delay period has not expired

Posted by blakhani on August 21, 2014


During my last visit to one of my friend’s home I was surprised to hear that he had a problem in log shipping where he wanted my help. It was a family meet-up and I never thought it would become technical. Anyways, I asked the problem and he mentioned that he has configured log-shipping for one database between two servers. He told me that he was getting some “delay” error and I asked to give the exact error message. Below is what we saw in “Restore Job history”

2014-06-21 06:14:30.45     Starting transaction log restore. Secondary ID: ‘8a3c7cae-6df2-4c6e-b528-2062d4b830f0’
2014-06-21 06:14:30.45     Retrieving restore settings. Secondary ID: ‘8a3c7cae-6df2-4c6e-b528-2062d4b830f0’
2014-06-21 06:14:30.45     Retrieved common restore settings. Primary Server: ‘SQLPAPA’, Primary Database: ‘LogShipDemo’, Backup Destination Directory: ‘E:\LS\Secondary’, File Retention Period: 4320 minute(s)
2014-06-21 06:14:30.45     Retrieved database restore settings. Secondary Database: ‘SomeName’, Restore Delay: 2, Restore All: True, Restore Mode: No Recovery, Disconnect Users: False, Last Restored File: E:\LS\Secondary\SomeName_20140621004200.trn, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified
2014-06-21 06:14:30.51     Skipping log backup file since load delay period has not expired. Secondary DB: ‘SomeName’, File: ‘E:\LS\Secondary\SomeName_20140621004231.trn’
2014-06-21 06:14:30.51     The restore operation was successful. Secondary Database: ‘SomeName’, Number of log backup files restored: 0
2014-06-21 06:14:30.51     Deleting old log backup files. Primary Database: ‘SomeName’
2014-06-21 06:14:30.51     The restore operation was successful. Secondary ID: ‘8a3c7cae-6df2-4c6e-b528-2062d4b830f0’
2014-06-21 06:14:30.51     —– END OF TRANSACTION LOG RESTORE     —–

So the question he asked were:

  • What is this "Load delay period" and what’s the meaning of the error?
  • Can we configure this somehow, somewhere?

Answer

  • Load delay is a period what is given to secondary server to wait to restore the backup file. We can use this if we want to wait upon the copy job.
  • We can set this value in the SSMS
    Database > Right click > properties -> Transaction Log shipping -> Secondary databases -> Restore Transaction Log

image

If we want to check via T-SQL then we can see that in MSDB database of secondary server.

Select  secondary_database, 
        restore_delay 
from    msdb.dbo.log_shipping_secondary_databases

If the load delay is set to zero and still we are seeing a long delay in restore job picking the file to restore then next thing to check would be if there is a time difference between primary and secondary servers. Having different time zone is not a problem but but UTC time should match else we would see such problems.

Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    3 Responses to “Log Shipping Message : Skipping log backup file since load delay period has not expired”

    1. Aman said

      Good one. Thanks for sharing 🙂

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: