In Log-shipping what is load delay period on secondary server - Skipping log backup file since load delay period has not expired ....
Category: sql server dr
iBarNor on Thu, 21 Jan 2010 16:50:03
During logshipping, job on secondary server is ran successfully BUT give this information
"Skipping log backup file since load delay period has not expired ...."
What is this "Load delay period" ? Can we configure this somehow, somewhere ?
NOTE : The value on "Restore Transasction Log tab", Delay Restoring backups at least = Default (zero minutes)
Jens Nilsson on Mon, 15 Feb 2010 09:16:43
Load delay is a period that you tell the secondary server to wait to restore the backup file.
You use this if you want to wait so up on the copy job.
You set tis value in the SSMS at
Database properties -> Transaction Log shipping -> Secundary databases -> Restore Transaction Log
or in T-SQL when adding a new secondary database use @restore_delay parameter like this
EXEC master.dbo.sp_add_log_shipping_secondary_database @secondary_database = N'testDB' ,@primary_server = N'mySERVER' ,@primary_database = N'testDB' ,@restore_delay = 2 ,@restore_mode = 1 ,@disconnect_users = 1 ,@restore_threshol
or if you want to change an existing secondary database you can also use @restore_delay parameter like this
EXEC master.dbo.sp_change_log_shipping_secondary_database @secondary_database = 'testDB' , @restore_delay = 2
Hope this will help you
iBarNor on Tue, 16 Feb 2010 10:03:33
Thanks for your response.
As i mentioned above that the settings are Restore Transasction Log tab", Delay Restoring backups at least = Default (zero minutes) So it should restore upto the latest transaction file available.
However, still i am getting this message "Skipping log backup file since load delay period has not expired ...". WHY ?
Following is the further explanation of situatation
The Log-shipping restore job runs successfull but last restored file is the which was copied 2 hours earlier. All jobs (backup, copy and restore) are set to run every 15 min automaticallly. For example
Last backup job ran at 0900
Last Copy job ran at 0900 (all files copied to the destination)
Last file restored by restore job = file_date_0700.trn (but i want to restore all files till 0900 without any further delay)
I tried manually several times the same job and getting following information all the time:
Skipping log backup file since load delay period has not expired ....
As said earlier restore delay period is set to ZERO. So the job should restore all the files available whenever the restore job runs.
unable to understand this information.
Vadim Solnyshko on Sun, 15 Aug 2010 16:51:56
Maybe too late to answer but I hope the solution will be helpful for smoebody else.
I encountered the issue described after many years I had stopped working with such a legacy as Log Shipping. -:)
In short, there's a bug in the SQLLogShip application. Sometimes depending on the machine time it gets a negative number and checks if it's less than tha value of the msdb.dbo.log_shipping_secondary_databases.restore_delay column.
Try to apply the following script on the secondary server:
[restore_delay] = -2000000000
kakaroto2012 on Tue, 17 Dec 2013 19:18:00
Thank you Vadim, it was so nice.
You have eliminated my headache
Mamku on Thu, 09 Jan 2014 07:05:22
thank you very much I also have the same problem and couldn't understand what was the problem when I sow this article I will try and I hope it will help me thanks again
Mamku on Thu, 16 Jan 2014 06:02:02
I did this changesbut it still lefts one already copied last transaction log file so this changes doesn't works.
Bhardwaj Ajay on Mon, 14 Apr 2014 15:39:38
I don't know whether this issue resolved for all or not.
Few days back I also faced the same issue but updating table didn't help me in this regard.
So I restarted the SQL Server Service as it is fail over environment for me and hit the restore job again.
If didnt help i was planning apply the .trns manfully and than hit the job again but fortunately it worked after restart.
Restore Log [Your DB Name] FROM DIsk ='Path and filename of trn' WITH STANDBY = 'path and file name for standbyfile.BAK', REPLACE
Hope it will help in future if someone come across this issue.
duanelf on Mon, 26 May 2014 00:33:06
I saw this pretty decisively on two servers with widely varying time zone configuration. So I had a bunch of transaction logs stacked up on the secondary that were not being restored, even though restore_delay set to 0.
I did the set to -2000000000 trick and sure enough on the next execution of the LSRestore job on the secondary, it found and restored ALL transaction files (even those from the "future" i.e. that were dated ahead of the system time on that machine)
Now I have been fiddling with the times - fortunately this is a test environment - the secondary is a VM in Azure and is using UTC. The primary is in the time zone of it's physical location and most of the folks using it. The log shipping process is still chugging away, but I now have several trn logs with times from _before_ transactions logs that have already been backed up, shipped across and restored on the secondary. I expected this could happen with the datetime changes, but I was curious as to how SQL Server would handle it... is the restore based on the name of the trn file? time stamp in the metadata? or perhaps a sequence number that would allow a bit more time independence?
Alas, the LSCopy job does not see these latest transaction logs, which are dated prior to ones that have already been copied and restored. I fear they will just sit there in the source directory and never get picked up. I suspect a possible solution would be to copy across and restore manually until the time on the primary gets beyond what has already been restored. I will probably try that unless I turf up a better solution. It's not an unexpected outcome, but not very resilient when changing time and date on the servers involved.
duanelf on Mon, 26 May 2014 02:24:12
How to get the LSBackup, LSCopy, and LSRestore jobs back in sync...
Last I posted the issue was that my trn backups were not being copied from Primary to Secondary.
I found upon further inspection of the LS related tables in MSDB the following likely candidates for adjustment:
1) dbo.log_shipping_monitor_secondary, column last_copied_file
change last copied file column to something older than the file that is stuck. For example, the value in the table was
I changed last_copied_file to E:\SQLLogShip\myDB_20140525235000.trn. Note that this is just a made up file name that is a few minutes before the actual file that I would like to restore (myDB_2014525235428.trn). 4 mins and 28 seconds before, to be exact.
LSCOPY runs and voila! now it is copied from primary to secondary. That appears to be the only change needed to get the copy going again.
2) For LSRestore, see the MSDB table dbo.log_shipping_monitor_secondary, change last_restored_file
again I used the made up file E:\SQLLogShip\myDB_20140525235000.trn
LSRESTORE runs and my just copied myDB_2014525235428.trn is restored
** note that
dbo.log_shipping_secondary_databases also has a last_restored_file column - this did not seem to have any effect, though I see that it updates after completing the above and LSRestore has run successfully, so now it is correct as well
3) LSBackup job is still not running, the job still has a last run date in the future. Could just leave it and eventually it will come right, but I made a fairly significant time change, plus it's all an experiment....back to MSDB.
look at dbo.sysjobs, get the job_id of your LSBackup job
edit dbo.sysjobschedules - change next_run_date next_run_time as needed to a datetime before the current time, or when you would like the job to start running.
I wouldn't be so cavalier with data that was important, but that's the benefit of being in Test, and it appears that these time comparisons are very rudimentary - a value in the relevant log shipping table and the name of the trn file. That said, if you were facing a problem of this nature due to lost trn files, corrupted, or some similar scenario, this wouldn't fix your problem, though it _might_ allow you to continue? But in my case I know I have all the trn files, it's just the time that changed, in this case on my Primary server, and thus the names of the trn logs got out sync.
Janderson Mira on Mon, 13 Feb 2017 21:13:18
Thank you Vadim.
Seven years and your post still helping.
Congrats from Brazilian IT friends.