What happens to transaction log backups during full backups?

TL;DR

Unless you’re on SQL 2000, don’t worry about scheduling log backups during full backups
Log backups during full backups won’t truncate the transaction log
You want to keep taking log backups in case your full backup fails

The first time I ever set up backups

Was, unfortunately, using a maintenance plan. All of the databases were in simple recovery. It was largely used for staging and tenderizing data during ETL. No log backups need apply.

Fast forward a bit, and I’m setting up backups for a server where losing 30 minutes of data could set a project back several hours. We’re now hiring log backups.

You're hired!

You’re hired!

So there I was, dutifully creating extravagant maintenance plans, pondering the miracle of the differential backup, and the grand eloquence of log backups. They were running every 10 minutes, those log backups.

Every 10 minutes.

Even during full backups.

I’m a developer and what is this?

  • What is SQL going to do with those?
  • Do I have to restore them?
  • Should I pause log backups during full backups?
  • Will this break something?
  • How much Laphroaig do I have to drink to forget I thought about this?

This was confounding to me. So I did some digging. Back in the SQL 2000 days, this could have gotten weird. But I was, thankfully(?) on 2005. Heh. Yeah. 2005. I know.

After 2005, you can totally, absolutely, 100% take log backups during full backups. You can take them and nothing will break and you won’t have to restore them (unless your full backup fails).

Wanna see a demo?

Of course you wanna see a demo. This one is a little more complicated than usual. It will require several-SSMS-tab technology.

I usually keep my SO database in simple, because I do horrible things that I don’t want to fully log. Fun fact: if you switch from simple to full recovery model and don’t take a full backup, you’re basically still in simple recovery. You should think about that for a minute and then take steps to avoid getting fired. Backing up SO takes me about 3.5-4 minutes.

In another window, kick off some log backups 1 minute apart. Note that doing this won’t overwrite log backups, it will stack them all within a single file.

You can verify this behavior by running the RESTORE HEADERONLY command at the end of this block. If you want to restore a particular backup out of a file with multiple backups in it, you use the position column and specify it with FILE = [n], which you can read more about here.

Just so you know I’m not pulling any shenanigans, let’s generate some log activity. This will dump 100 rows into a dummy table every 30 seconds. It is neither pretty nor elegant.

When that’s all done, you can run something like this to see what happened. You’ll probably have to replace the date. I wrote this, like, two or three weeks ago by now.

When you take a full backup, the first thing it does it issue a checkpoint. That’s why the full and all subsequent log backups have the same checkpoint LSN. The first four log backups all have the same database backup LSN because they occurred during the full backup. That doesn’t change until the full is done.

RED SQUARES AT NIGHT

RED SQUARES AT NIGHT

For toots and snickers, I ran this all a second time, and cancelled the full backup halfway through. The full backup issued a new checkpoint, so the checkpoint LSN changes, but the database backup LSN never changes, because it got canceled. That means taking log backups during full backups is totally useful. If your full backup fails for whatever reason, these things keep the chain alive.

NOTHING CHANGES

NOTHING CHANGES

If the third time is to be a charm, and it is, the same thing occurs as the first run. New checkpoint LSN, and the database backup LSN runs through until the backup finishes. You can verify that by looking at the start and end times columns.

I'm always convinced that whoever came up with the term LSN Chains really liked 90's Grunge.

I’m always convinced that whoever came up with the term LSN Chains really liked 90’s Grunge.

If you still don’t believe me

Just look at sys.databases while you’re running a full backup.

Yes, I made you read all that to get here.

Yes, I made you read all that to get here.

The result is acknowledged, though not documented, here. This does indeed mean that log truncation will not occur during a full backup even if you take log backups. It will happen when you take the first log backup after the full finishes. You may want to consider this when scheduling maintenance items that may hog up log space alongside full backups.

Recap

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.

Previous Post
Give Your T-SQL a Semicolonoscopy
Next Post
Who’s Really Doing Continuous Integration of SQL Server Databases?

24 Comments. Leave new

  • Wow, I had to say “LSN Chains” to myself a few times to get that.

    Reply
  • Do you guys have any snippets or tutorials on how to quickly identify backup sets? (F + D + L + L + L) and (F + L + L + L)

    Reply
  • This is very interesting. It is one of those things you “just knew” but were afraid to ask the details on. Thanks!

    Reply
  • Lars Rasmussen
    December 22, 2015 4:34 pm

    Red Squares At Night? Now I can’t get that song by The Fixx out of my head.

    Reply
  • Isn’t it also true that the log backups that happen during the full backup are useful if you need to restore to a point in time between the start and end time of that full backup?

    Reply
  • Its really interesting and helpful…
    Thanks!!!

    Reply
  • This is very helpful to understanding why I have a huge log backup during a full backup that was taking up so much space! The solution was to schedule my log shipping to start after the full backup. For a long time I did not understand why there was a huge log backup after a full backup, but only to realize that it was during a full backup that was causing this.

    Reply
  • THIS was one of the most helpful articles I ever read. Thanks.

    Reply
  • Just catching up on this, great article thank you. I was actually looking for a solution to a related issue. When we add a new database (restore from a staging instance) to our live instance we have a job which sets it to Full recovery (in case the tech forgot that) and then a maintenance plan that runs a Full backup each night. We also have a maintenance plan that runs a transaction log backup every 30 minutes. How do I stop the latter failing whilst we are waiting for the first Full backup to complete? I’m thinking I maybe cannot achieve this with maintenance plans but need a bespoke agent job?

    Reply
  • “Log backups during full backups will not truncate the log. That has to wait until the first log backup after the full finishes.”

    could you replace “full” with “differential” here? is the way backups function the same in regards to when new transaction logs start? i am having trouble understanding how a restore would work, because i keep asking “how do you get a clean cutoff point between the end of a differential backup and the start of the very next transaction log?”

    Reply
    • eric — LSNs are a hell of a thing.

      Think about a scenario with Log Shipping: If you take a differential back up on the primary and apply it to the secondary, which log file would you try to restore next?

      Reply
      • if i just restored a diff, that means i have already restored a full. so i would restore the first log file that was backed up after the diff? i mean, i know that the backup software is doing this for me but i need to wrap my head around it so i understand. thanks for responding 🙂

        Reply
  • Waqqas Khokhar
    March 19, 2019 1:51 pm

    “I’m always convinced that whoever came up with the term LSN Chains really liked 90’s Grunge.”

    L-O-L. Thanks for the laugh and helping me feel less old!

    Reply
  • John Zabroski
    April 3, 2019 9:38 am

    > How much Laphroaig do I have to drink to forget I thought about this?

    Most accurate statement ever.

    Reply
  • […] – transaction log file not shrinkingsource – transaction log backup during full […]

    Reply
  • Hi Erik,
    I have primary database already set with replication and we have already jobs for Full(weekly), Diff(night) & log(1 hrs) backup plans. Now I want to set up another copy of the same database as read only mode not for DR. May I go with log shipping by using same log backups without changing any previous setup.

    Reply
  • Susan Brower
    May 29, 2019 10:36 am

    Similar question: We have a very large database with a very large (over 1.5 TB) log. A transaction log dump was started, and while it is running, a full DB backup was run (successfully). The log dump is still running. If the log dump is killed and we truncate the log, at what point will transactions be lost? After the full dump started?

    Reply
  • […] View Image More Like This […]

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}