patternsqlMinor
Minutely Log Backups - Missed Jobs
Viewed 0 times
missedbackupslogminutelyjobs
Problem
I tried searching for this but couldn't find an existing question about this. I'm considering implementing Brent Ozar's suggestion to backup minutely for our critical databases. I don't have a great test environment where I can try this and get useful results. I'm wondering if the log backup job runs over one minute, what happens to the next minutely backup job(s). In these situations, I'm assuming the jobs will fail but I'm worried they'll queue up and I could wind up with a vicious circle where I can never get caught up on the backups. Can anyone else advise if how SQL Server will handle this situation? Also, if the jobs fail, does it fail "elegantly" (i.e., the backup chain is not broken and I can continue to restore using the logs) or will it bugger it up and break the backup chain?
Also, if I have full and differentials scheduled throughout the day as well, and a minutely log is running will it keep the full and diffs for running?
I appreciate any help you all can provide.
Also, if I have full and differentials scheduled throughout the day as well, and a minutely log is running will it keep the full and diffs for running?
I appreciate any help you all can provide.
Solution
You did not mention your workload, but I run log backups every minute in our OLTP environment without issue (granted, we don't process thousands of transactions per second). If a log backup happens to take over 1 minute, there is no failure. The job will merely run again on the minute mark after the current job completes.
As Brent says in What happens to transaction log backups during full backups?
Backups are beautiful things. You should take full ones, and probably
differential ones, and if you’re in full recovery model, definitely
log ones. How often you take them is up to you and your boss. Or maybe
their boss. But it’s definitely not up to you. Unless you’re your
boss.
Log backups during full backups won’t hurt anything, and may end up
helping things if your full backup fails, and you need to restore
something.
Log backups during full backups will not truncate the log. That has to
wait until the first log backup after the full finishes.
Some additional information regarding Understanding Schedules and Long-Running Jobs, which I believe still holds true today:
What happens when a scheduled job runs longer than the scheduled
interval time period? For example, if a job is scheduled to execute
every 10 minutes and takes 35 minutes to complete, does the job
execute again while it's still running?
On the SQL Server Most Valuable Professional (MVP) newsgroup, a
private forum for SQL Server MVPs, Microsoft published two design
facts about the SQL Server Agent that can help answer this question.
First, the SQL Server Agent runs only one instance of a job at a time.
Second, the SQL Server Agent calculates the Next Run Date at the end
of the job. So, if a job execution takes longer than its scheduled
interval, the job will rerun at the next recurrence point. For
example, imagine that Job A is scheduled with a 1-minute recurrence
interval. Job A starts at 1:25 p.m. and runs for 1 minute 45 seconds.
Job A is still running at 1:26, when the next scheduled recurrence
should happen, and the SQL Server Agent will run only one instance of
a job at a time—so nothing happens at 1:26 p.m. When Job A ends at
1:26:45 p.m., the SQL Server Agent resets the Next Run Date. Based on
the specified 1-minute recurrence interval, the next time the job will
run is 1:27 p.m.
As Brent says in What happens to transaction log backups during full backups?
Backups are beautiful things. You should take full ones, and probably
differential ones, and if you’re in full recovery model, definitely
log ones. How often you take them is up to you and your boss. Or maybe
their boss. But it’s definitely not up to you. Unless you’re your
boss.
Log backups during full backups won’t hurt anything, and may end up
helping things if your full backup fails, and you need to restore
something.
Log backups during full backups will not truncate the log. That has to
wait until the first log backup after the full finishes.
Some additional information regarding Understanding Schedules and Long-Running Jobs, which I believe still holds true today:
What happens when a scheduled job runs longer than the scheduled
interval time period? For example, if a job is scheduled to execute
every 10 minutes and takes 35 minutes to complete, does the job
execute again while it's still running?
On the SQL Server Most Valuable Professional (MVP) newsgroup, a
private forum for SQL Server MVPs, Microsoft published two design
facts about the SQL Server Agent that can help answer this question.
First, the SQL Server Agent runs only one instance of a job at a time.
Second, the SQL Server Agent calculates the Next Run Date at the end
of the job. So, if a job execution takes longer than its scheduled
interval, the job will rerun at the next recurrence point. For
example, imagine that Job A is scheduled with a 1-minute recurrence
interval. Job A starts at 1:25 p.m. and runs for 1 minute 45 seconds.
Job A is still running at 1:26, when the next scheduled recurrence
should happen, and the SQL Server Agent will run only one instance of
a job at a time—so nothing happens at 1:26 p.m. When Job A ends at
1:26:45 p.m., the SQL Server Agent resets the Next Run Date. Based on
the specified 1-minute recurrence interval, the next time the job will
run is 1:27 p.m.
Context
StackExchange Database Administrators Q#212049, answer score: 5
Revisions (0)
No revisions yet.