1

I have setup transaction log shipping between two servers every 15 minutes, this is on SQL Server 2008 R2. The problem is that when the logs are being restored on the secondary server, it always uses the second-most-recent log file rather than the most recent one available. I suspect this is because the restore happens at the same time the backup occurs on the primary server. I thought I could use the "delay" option to get around this, but it doesn't seem to do the trick. What am I missing?

  • You can change the restore frequency to be more frequent on secondary server, but then it wont keep up with the 15 min frequency of backups on primary server. Out of curiosity, what problem do you see with that ? Logshipping will pick up the logs (.trn and not the .wrk files) to restore. – Kin Shah May 23 '14 at 20:13
  • that's an interesting idea, so theoretically, i could change the frequency of the restore of the second server to 5 minutes, so that it always falls at the 15 minute mark. I'll give that a go and report back. – M Bartolomeo May 23 '14 at 20:33
  • Msg from Job ::: 2014-05-23 17:00:15.68 Could not find a log backup file that could be applied to secondary database 'VisionTLOG'. ::: So this must mean that my timing is too exact and the log isn't yet available. Is there any way to get the schedule to "delay" a minute or two before starting the restore job? – M Bartolomeo May 23 '14 at 21:04
  • Changing the restore schedule to more frequent does the trick. Though it seems cumbersome for the server to constantly check for new logs. Wish the delayed option worked better. Thank you Kin!! – M Bartolomeo May 23 '14 at 21:12
1

Converting my comment to answer:

can change the restore frequency to be more frequent on secondary server, but then it wont keep up with the 15 min frequency of backups on primary server. Logshipping will pick up the logs (.trn and not the .wrk files) to restore

  • 2
    Hi there, I did more testing and found a better answer. When you setup the restore schedule on the secondary server, instead of starting the daily schedule at 12:00:00, start it at 12:02:00. SQL calculates this as 17, 32, 47, and 02 of every hour when using 15 minute interval. This way, your secondary database's restore job matches the primary database's backup schedule duration. – M Bartolomeo May 23 '14 at 22:59
  • 1
    You would need to adjust this offset to accomodate for the approximate time it takes to perform each log backup. – Max Vernon May 23 '14 at 23:46
  • @MBartolomeo you have to do some testing on what works in your environment. To be frank, we use log-shipping for all our PROD databases and till today, I didn't have to modify schedules of restore jobs (we run it every 1 mins). Logshipping just works fine. – Kin Shah May 26 '14 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.