0

When we configure the Log Shipping, there are jobs automatically created that run on schedule. What happens if db size grows or there is network latency, does SQL Server auto handle the job schedules?

Example: What happens if backup is not ready, will the copy job still run. Similarly, what if the copy is not compete - will the restore job still run? Will SQL Server dynamically handle this or will it skip one round?

1

All the Log Shipping jobs run on their fixed schedule, while the number of files they process each time may be variable. The Copy jobs perform a "best effort" copy of all files that haven't been copied yet and aren't currently in use. The Restore jobs perform a "best effort" restore of all the files available that haven't been restored yet. However, that is contingent on adhering to any other rules you may have specified (ex. Setting a restore delay of 2 hours so you can possibly retrieve data off your Secondary if someone does an unintentional change/delete of data on the Primary).

Essentially, you can think of each job as taking a kind of Work Queue approach that first creates a list of work to perform before processing each item on that list. If a transaction log backup is still writing to file DBTranLogBackup.bak at the time the Copy runs, that particular file will not make it onto the list of work that can be performed and will get processed during the next run of the job.

Hope that helps.

  • I have staggered my backup, copy and restore jobs to account for the possibility of one starting before other finished. – Bob Klimes Oct 10 '18 at 14:09
  • Since it is not one job with 3 steps that execute sequentially. What you mean by best effort please? What happens if copy job doesnt hav backip ready or if restore job starts when copy is in progress? – variable Oct 15 '18 at 18:53
  • My intent was to help explain the behavior in my second paragraph above. What I meant by "best effort" for each job is that it will act upon all the files it can that are not currently in use by one of the other jobs. Therefore, if your Copy job is in the process of copying file A.trn while the Restore job kicks off, the Restore job will simply not try to restore that file during its current run. The next time it kicks off, it would likely be able to process A.trn as the Copy job should be finished by then. – Micah Nikkel Oct 15 '18 at 20:32

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.