We will have an application that will go live soon. I will be using Log Shipping for Disaster Recovery but I am confused about the Job scheduling. The transaction logs will be taken every 10 minutes and is expected to have around 15MB files generated every time. Now if I schedule the Jobs as follows

  1. Back up Every 10 Minutes -----Network Share
  2. Copy every 15 Minutes----- Local Disk
  3. Restore Every 20 Minutes

Now at any point in time there will be a maximum data loss of 10 minutes if the primary goes down. Is there any way to reduce that time and also at the same time make sure the job does not collide with each other... Any expert opinion will be greatly appreciated.

  • 1
    You can back up your logs as often as you like - want to do it every minute? Now you're down to max data loss of one minute (+ however long the log backup takes, since if the data center explodes while it is writing...). The last step of the backup job should be to copy/move the log backup from the place you wrote it to a pickup folder. This way your copy job can also run as frequently as you like, and you don't have to worry about it trying to copy a log backup that isn't finished yet. Why it's every 15 minutes, not sure, since they will collide every second run anyway? – Aaron Bertrand Oct 12 '18 at 15:03
  • 2
    The restore job is different. Having the backups available for disaster recovery if you need it, and making sure the log shipped copy is always as current as possible, are two different things. Typically the former isn't absolutely necessary, especially because it impacts your ability to use that log shipped copy for other read-only operations like reporting. So you may want to run that restore job less frequently so you don't have to kick users out every 20 minutes. I wrote about getting around this: sqlperformance.com/2014/10/sql-performance/… – Aaron Bertrand Oct 12 '18 at 15:03
  • Thanks a lot for clearing a major confusion. So I can delay the restore as the main thing is to have the copy job copy the back up files so that in the case of a disaster I always have a recent backup copy witout much delay . So in the above example I can Back up every 5 Minutes and set the copy job run every 3 Minutes ...Please correct me if that does not make sense – SQL_NoExpert Oct 12 '18 at 15:44
  • 2
    Well I don't know why you would ever run the copy job more frequently than the backup job. Many times the job will start with nothing to do. Why not also run the copy job every 5 minutes, just offset from the backup job by a minute, or two, or three? That way it should be copying exactly one file every time it runs (unless your backup job ever runs longer than [offset]). – Aaron Bertrand Oct 12 '18 at 16:00
  • Pardon me ,,,so I can for example have back up start at 12 am every 5 minutes and the copy start at 12.02 am every 5 minutes in that case the copy jobs runs after 2 minutes of the back up job and my maximum data loss can be 6 minutes – SQL_NoExpert Oct 12 '18 at 16:33

Your Answer

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

Browse other questions tagged or ask your own question.