1

I am editing my previous question to be more precise as i am still unable to get the logic behind the log shipping restore job.

May be there is something i am missing to explain but will try my best.

We have night Log Shipping restore job running on secondary at 10:00 PM daily once. When it starts to restore it picks the log backups from where it completed on its last run. I understand how log restores are done per Kins link, but i see LS restore will try to get as close to current run time and completes. But that time varies with a gap of 30 min, 20 or even an hour sometime.

Its still not clear how ls restore is checking this is the best time it will complete. This is to let users know till what time of the day data is getting restored via LS job, so that they can query and fetch data accordingly from Standby database:

Previous question marked as duplicate @ How log shipping restore works

Eg: What i see is LS restore completes at 3:30 AM. For log backups restored , i see the files which arrived at 2:45 AM only restored but not the one at 3 and 3:15 AM. So just thinking if these 2 files are copied, why LS restored ignored them and got completed. LS backup and LS copy happens every 15 mins while restore once at10PM.So i need to understand how is LS restore deciding on what basis will it complete?

Improve this question
4
  • So there are multiple things in your question, you want to know how SQL Server restore job calculates time, well that is done internally and may change depending upon what is stored in log file and if rollback needs to be done using tuf file. For 2nd question does backup and copy jobs run even after 10:00 PM, when LS restore job runs ?
    – Shanky
    Sep 21, 2018 at 12:55
  • Shanky@ Thanks. yes LS backup and copy runs every 15 mins
    – BeginnerDBA
    Sep 21, 2018 at 13:42
  • Is the secondary used for read when the logs are not restored or is it always in restoring mode (not accessible for read only queries) ?
    – Kin Shah
    Sep 25, 2018 at 15:11
  • @Kin - yes secondary is used for read when the logs are not restored
    – BeginnerDBA
    Sep 25, 2018 at 22:33

1 Answer 1

Reset to default
0

I would try utilizing the built-in stored procedures to investigate and hopefully correct this.

For example, try running this on the Secondary server while in the master database: 

EXEC sp_help_log_shipping_secondary_database @secondary_database = '<database name>'

This will return a result set that contains a column named 'restore_all' that may be of interest to you. If that is 0, then when the restore job runs it will stop after restoring only 1 transaction log file. Otherwise, it will apply as many as possible.

If it is configured incorrectly for your situation, you can change it like this:

EXEC sp_change_log_shipping_secondary_database @secondary_database = '<database name>', @restore_all = 1

Hopefully, this will help you correct your issue or at least give you a boost in your search for the root cause.

Improve this answer
1
  • This is correct .. so why downvote ? The only reason why the tlog files will not be restored is if the .wrk file is large or if delay logs is set on secondary or if restore_all is set to 0.
    – Kin Shah
    Sep 26, 2018 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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