A Full Backup can Impact a Log Backup–MCM Prep

In the early days of SQL Server you could not run a log backup while a full backup was running. In fact,you tried to schedule them apart from each other early in my career to prevent collisions. Nothing bad happens, but it does cause failures in your monitoring and those are annoying.

In modern versions of SQL Server, you can run log backups and simultaneous full backups. They don’t collide or block each other, but there is a way that the full backup impacts a log backup.

If you start a full backup, once all data pages are written to the backup device, the log records that were created since the start of the full backup are added to the full backup file. This allows the redo/undo process to complete and this gives you a full backup set that is intact as of the point in time in which the data pages finish writing.

If you run a log backup, typically the log records are written out, and then the VLFs in the log file that were written to disk are marked as inactive.

However since those log records are needed for the full backup to be complete, the log backup cannot clear those VLFs when the log backup finishes. That process still occurs, and technically this is part of the log backup, but it is deferred until the full backup completes.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

2 Responses to A Full Backup can Impact a Log Backup–MCM Prep

  1. mohammed moinudheen says:

    Could you please clarify on below

    – As per Steve’s update,a normal log backup clears out the VLFs that were backed up, except for the most recent active one.
    If a full backup is running, that clearing process is deferred

    Question
    ——–
    The clearing process of VLF’s is deferred until the next log backup runs?
    Or is it done automatically once the full backup completes successfully.

  2. way0utwest says:

    The VLFs are cleared when the full backup is complete.

Comments are closed.