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?
4
-
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 ShahCommented May 23, 2014 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 BartolomeoCommented May 23, 2014 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 BartolomeoCommented May 23, 2014 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 BartolomeoCommented May 23, 2014 at 21:12
Add a comment
|
1 Answer
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
answered May 23, 2014 at 21:27
-
2Hi 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. Commented May 23, 2014 at 22:59
-
1You would need to adjust this offset to accomodate for the approximate time it takes to perform each log backup.– Hannah Vernon ♦Commented May 23, 2014 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 ShahCommented May 26, 2014 at 23:18