SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

Filed under: Blog Tagged: Backup/Recovery, mcm, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by Dr. Diana Dee on 5 February 2011

The first thing a full or differential backup process does is issue a checkpoint.  This moves the minLSN as close to the end of the active portion of the T-log as possible.

The VLF containing the minLSN T-log record is part of the active portion of the T-log, and its records cannot be marked as truncated.

When the full backup process finishes copying all non-empty extents to the backup file, the log records from the minLSN to the end of the T-log are recorded in the backup file.

A T-log backup will not change the value of minLSN.  The T-log backup will copy all T-log records since the previous T-log backup to the end of the T-log at the time of the T-log backup.

A T-log backup will therefore not mark the VLF containing the minLSN as truncated, so there will be no conflict.

Do you have a different viewpoint?

Posted by Paul Randal on 5 February 2011

Diana - I'm not sure what point you're trying to make, but it's orthogonal to what Steve is saying, which is 100% correct.

Steve is making the point that if t-log VLFs become *able* to be truncated while the data backup is running, i.e. if some transactions commit making a VLF able to be truncated except that its log hasn't been backed up by a t-log backup, and then the t-log backup occurs, because the data backup needs the log back to the minLSN from the its checkpoint, that VLF that was just backed up by the t-log backup can't be marked inactive, even though a t-log backup has backed it up. Hence the log may have to grow because of a long-running data backup. This is a common issue that people run into, and an additional reason to use shorter diff backups.

A correction to what you're saying: the log reading portion of a data backup does not copy log records all the way to the end of the t-log, it only copies up to the LSN at the point in time when the data reading portion of the data backup completed.


Posted by Steve Jones on 6 February 2011

Perhaps I didn't explain it well, but I was trying to note what Paul confirmed. 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.

I learned this from Mr. Randal, and it was one of those things that I had never really understood before studying for the MCM.

Posted by mohammed moinudheen on 7 February 2011

Could you please clarify on below

As per Steve's comment, 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



Is the clearing process of VLF's deferred until the next log backup runs? Or is it done automatically once the full backup completes successfully.

Posted by Suvendu on 7 February 2011

Could you please clarifying the below..

1. What is written inside log file.

2. How frequent log truncate in simple recovery mode .

Posted by Steve Jones on 7 February 2011

@mohammed, AFAIK, the log VLFs are cleared when the full backup completes. The operation is linked to the log backup that occurred when the full backup was running, but the operation is like a restart pending operation in Windows. Once the full completes, the log VLFs that were backed up are cleared.


1. The log file contains records of all the operations performed on the server that change data.

2. The log "truncates" after each checkpoint. msdn.microsoft.com/.../ms189573.aspx

Posted by mohammed moinudheen on 7 February 2011

Thank you Steve for clarifying

Posted by DLathrop on 7 February 2011

One clarification to Steve's last post:

1. The log file contains records of all the operations performed on the server that change data or any database structures.

For example, if you rebuild an indexes in full recovery mode, even if no data rows are being changed, you will see a lot of T-log activity. This is because SQL Server is recording page allocations and deallocations, index entry deletion and creation, moving data rows (for clustered indexes) etc. So if you rebuild all indexes in a database, your T-log may suddenly be as big as the data files.

Posted by Subhash Pant on 7 February 2011

Is there a time when the VLFs dont get marked as inactive, and checkpoint does not flush logs?



Posted by Suvendu on 9 February 2011

Thanks Steve,

Could you please tell me what happend in Bulk log model.

1. While bulk logged recovery model what is written inside Log file.

Posted by Steve Jones on 9 February 2011

Bulk logged has nothing to do with full/log backups. The behavior is the same.

VLFs get marked as inactive when the log backup runs, or a truncate occurs in SIMPLE mode. The CHECKPOINT does not flush logs, it does a truncate in SIMPLE recovery mode, and flushes pages in other recovery models.

Posted by Suvendu on 11 February 2011

Thanks Steve...

1. What is check point and lache.

2. What is index and page file.

Posted by Steve Jones on 11 February 2011

Thanks for your comments, but this isn't the place to ask questions about other topics and learn SQL Server. Please use Books Online or Google to research these topics, and ask specific questions at www.sqlservercentral.com/forums. Don't ask "what is an index file?" Try to learn something yourself and ask a more specific question about what you don't understand.

Leave a Comment

Please register or log in to leave a comment.