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?
- 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
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!