SQL Server – Minutely Log Backups and Missed Jobs

backupsp-blitzsql server

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.

Best Answer

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.

Related Question