sqlskills-logo-2015-white.png

Search Engine Q&A #16: Concurrent log and full backups


Here’s a question that came in – what changed in SQL Server 2005 that allows concurrent log and full backups?


First a little background, in case you didn’t know about the change in behavior. In SQL Server 2000, a concurrent log backup with either a full or diff backup (I’ll just say ‘full’ from now on but take it to mean ‘full or diff’) was not permitted. The reason is that a log backup would clear the inactive portion of the log once it’s been backed up, but a full backup may still need some of that log so it can’t be cleared (see this post and this post for an explanation). The simple route was taken of disallowing concurrent log backups with fulls.


In SQL Server 2005, the restriction was lifted, but there’s a subtle twist. You can do concurrent log backups with fulls BUT the log is not cleared when the log backup ends. The clearing of the inactive portion of the log is delayed until the full backup completes. This could cause you to have disk space problems if your log generation rate is huge and you’re relying on very frequent backups to manage the log size.


So – what changed that allowed the SS2000 restriction to be lifted? Nothing – just the code was changed to delay the log clearing and allow the concurrent backups.


Pretty cool change – but watch out for the twist.

11 thoughts on “Search Engine Q&A #16: Concurrent log and full backups”

  1. Yeah, sometimes little tweak can make a big deal. Sometimes, however, it may make a crash. What’s changed in SQL Server 2008 about this? Anything interesting around this?
    Thanks a bunch,
    Carl

    Reply

  3. Hi Paul

    Have you ever encountered a case or know of one with Litespeed where a Full and Log Backup kicked off at the same time resulted in a corrupted Log backup… we have observed this in a couple of cases and were wondering if the simultaneous kick-off was the cause as on each occasion the log backup was corrupted a full backup had kicked off at the exact same time as the log backup.

    Many thanks.

    Cheers
    Chirag

    Reply

  4. Can’t comment on LiteSpeed as I’ve never used it – I’ve heard it’s had issues in the past but I haven’t heard of this specific case. This certainly isn’t a SQL Server issue that you’re experiencing. Thanks

    Reply

  5. Paul,

    The full recovery procedure is clear when the backups all run sequentially. But if a log backup does run concurrently with a full or differential backup, how do you know if that log backup is required for a full recovery?

    Thanks,
    Eric

    Reply

    1. By the LSNs in the backup header (from RESTORE HEADERONLY or the msdb..backupset table). The first log backup restored after a full or diff needs to have the data backup’s lastLSN + 1 contained within its LSN range.

      Reply

  6. Paul, if understand correctly from above discussion, incase of concurrent full(or diff) & log backups, the inactive portion of log should go to full backup rather to concurrent log backups, right? so that makes the full backup size is sum of actual data pages allocated+size of inactive log? please correct me if I am wrong.

    Reply

    1. That’s not correct. Full and diff backups include as much log as is necessary to run crash recovery. They have no effect on how much log is included in a log backup. Log backups and data backups to not affect each other at all.

      Reply

  7. Great information. Thank you.

    I’m trying to build a concurrent backup compatibility chart. And I need some additional help. The reason for this need is two fold:
    1. Our FULL backup process executes a log backup (for DBs in FULL recovery) right before taking the FULL – it was mainly to reduce the full backup size (but we previously understood it would it improve point in time recovery)
    2. Our backups can get triggered by more than 1 source (which is altogether too long of a story to include here)

    (Hopefully this will format properly)
    FULL DIFF LOG FULL-COPY_ONLY
    FULL ?? ?? OK ??
    DIFF ?? ?? OK ??
    LOG OK OK ?? OK
    FULL-COPY_ONLY ?? ?? OK ??

    — I previously had the understanding that:
    — 1- A LOG backup can run concurrently with either a FULL or DIFF, but not with another LOG
    — 2- A DIFF or FULL backup can NOT run concurrently with either a FULL or DIFF, but can with a LOG
    —
    — However, I have observed that SQL does seem to wait at least a short time when 2 FULLS are started about the same time.
    — (The scenario I observed was starting a FULL, then starting another FULL within a second or 2; the first FULL finished after about 8 seconds and then the second FULL took about 17 seconds total clock time;
    — an isolated FULL of this DB takes on average about 8 seconds – so it appeared that the second FULL was just waiting for the first FULL to finish)

    Any help you have to help me fill in the gaps would be much appreciated.

    Thanks for your help. May God bless you abundantly,
    David

    Reply

    1. You’re correct – log backup can run concurrently with data backup, but not two data or two log concurrently. The second one started will wait until the first one finishes. Log backup before a full is not needed and will have no effect on the size of the full backup, which includes only as much log as necessary to crash-recover the database being restored.

      Reply

Leave a Reply

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

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course