There’s still a widely held misconception that when properly in the FULL or BULK_LOGGED recovery models that full or differential backups can truncate the log. No. It *NEVER* happens. This is one of the reasons why I’m doing a whole spotlight session on this at PASS this year – the transaction log and its behavior is IMHO one of the most misunderstood parts of SQL Server.
Notice that I said ‘when properly in the FULL or BULK_LOGGED recovery models’. If you switch recovery models to FULL or BULK_LOGGED, until you take the first full backup, you are still essentially in the SIMPLE recovery model, and so the log will truncate on checkpoint. Once you take that first full backup, you are then in I-will-manage-the-size-of-the-log-through-log-backups mode. After that, the ONLY thing that will allow the log to clear/truncate is a log backup, as long as nothing else requires those transaction log records.
If you’re not familiar with the term ‘log clearing’ or ‘log truncating’, they mean exactly the same thing – part of the transaction log is marked as no longer needed and can be overwritten. Nothing is zeroed out, the log file size is not altered. Some background reading on this:
- My Understanding Logging and Recovery in SQL Server article in TechNet Magazine from February 2009. This also has a screencast demo that shows the log continues to truncate-on-checkpoint after switching to FULL and before a full backup has been taken.
- An editorial I wrote in April about Importance of proper transaction log size management.
- A pretty deep blog post I wrote about analyzing VLF sequences in the log: Inside the Storage Engine: More on the circular nature of the log
Earlier this week I was involved in a discussion about log backup size management and how to prevent a log backup following a maintenance operation to not contain details of the maintenance operation.
There’s a very simple answer: you can’t.
If you do an operation in the FULL or BULK_LOGGED recovery models, the next log backup will contain all information required to replay that operation. In the FULL recovery model, everything is fully logged, so the log backup will contain all the log records generated by the operation. In the BULK_LOGGED recovery model, you may perform a minimally-logged operation, which generates hardly any transaction log, but the next log *backup* will be about the same size as if the operation was fully logged – because the log backup will pick up all the data extents modified by the minimally-logged operation.
One point in the discussion was that if you’re running in the FULL or BULK_LOGGED recovery models, and you do a full backup after the maintenance operation, and before the log backup, the full backup will contain all the changes made by the maintenance operation, yes, and will clear the log.
A log backup is *ALL* the log generated since the last log backup. If this were not the case, how would log shipping work? You could take a full backup on the log shipping primary and suddenly you’ve broken the log backup chain and log shipping breaks. No, this is not how things work. A full backup contains only enough transaction log necessary to be able to restore that database to a transactionally consistent time – the time at which the data reading portion of the full backup completed. I blogged about this extensively previously:
- Debunking a couple of myths around full database backups (which also explains why the WITH STOPAT syntax exists for full and differential backups, but has no effect and is only there to allow you to put WITH STOPAT on all backups in a point-in-time restore sequence.
- More on how much transaction log a full backup includes
But you don’t have to believe me – it’s very simple to convince yourself. The following script will show you that a full backup has no effect on the transaction log. It does the following:
- Create a database and put it into the FULL recovery model, with a full backup.
- Create and populate and index.
- Take log backup 1 (just to clear things out)
- Rebuild the index.
- Take log backup 2.
- Rebuild the index.
- Take a full backup.
- Take log backup 3.
And we will see that log backup #3 is the same size as log backup #2. The full backup will make no difference whatsoever.
Here’s the script:
USE master; GO DROP DATABASE LogBackupTest; GO CREATE DATABASE LogBackupTest; GO USE LogBackupTest; GO ALTER DATABASE LogBackupTest SET RECOVERY FULL; GO BACKUP DATABASE LogBackupTest TO DISK = 'C:SQLskillsLogBackupTest_Full1.bak' WITH INIT; GO CREATE TABLE t1 (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a'); GO CREATE CLUSTERED INDEX t1c1 ON t1 (c1); GO SET NOCOUNT ON; GO INSERT INTO t1 DEFAULT VALUES; GO 1000 BACKUP LOG LogBackupTest TO DISK = 'C:SQLskillsLogBackupTest_Log1.bak' WITH INIT; GO -- Rebuild the index to generate some log and get a baseline ALTER INDEX t1c1 ON t1 REBUILD; GO BACKUP LOG LogBackupTest TO DISK = 'C:SQLskillsLogBackupTest_Log2.bak' WITH INIT; GO -- Now do it again, but take a full backup before the log backup ALTER INDEX t1c1 ON t1 REBUILD; GO BACKUP DATABASE LogBackupTest TO DISK = 'C:SQLskillsLogBackupTest_Full2.bak' WITH INIT; GO BACKUP LOG LogBackupTest TO DISK = 'C:SQLskillsLogBackupTest_Log3.bak' WITH INIT; GO
And here’s the result:
Log backup #3 is the same size as log backup #2. It contains all the log generated since log backup #2 was taken. The full backup had no affect whatsoever, because that would break the log backup chain.
If you don’t believe me, run the script yourself and you’ll see. A full backup does not and cannot affect the transaction log.