A Primer on Log Shrinking and Truncation in SQL Server

In the event that a transaction log grows to an out-of-control size, shrinking it can be the only solution – but don’t rush to do this until you are aware of the consequences. Shrinking the log could ‘break the log chain’ – rendering the existing transaction log backups useless. When this happens, the most recent recoverable data will be accessible only from the last database backup. Routinely shrinking the log file will also fragment the log file.

When shrinking the log becomes unavoidable, be sure to understand the aftereffects of doing so, and how to verify the integrity of the transaction log chain.

Truncate the Log

Why does a transaction log file grow in size? A transaction log is typically truncated under these scenarios:

• if the database is set to the SIMPLE recovery model, when a database backup is performed
• if the database is set to the FULL recovery model, when a log file backup is performed

If a database does not have regularly scheduled backups in place, the transaction log will continue to accumulate inactive transactions, and thus increase the size of the log file as needed – limited to the file size settings, and, of course, the size of the disk. So, an overgrown transaction log is a good indicator that backups are not being taken often enough, if at all.

Truncating a log file is not the same as shrinking it. Truncation removes the inactive virtual log files – which contain the data in the transaction log that have already been committed to the database. Remember, one of the benefits of having transaction logs is that they provide the ability to roll back a transaction before committing it to disk. After truncating the inactive portion of the log, however, the physical size of the log file remains the same. It’s a container that stretches in size to accommodate its contents, but never reduces in size.

Truncating the log essentially clears out space in the log file, but shrinking the log deflates the physical size of the file so that the empty space is removed. An older method of truncating the log was to use BACKUP LOG WITH TRUNCATE_ONLY or BACKUP LOG WITH NO_LOG. These methods have been deprecated in SQL Server 2005, and were completely removed from SQL Server 2008 – and for good reason.
Both of these methods issued a log backup command, but only truncated the log, not actually backing up the log. The problem with using these statements to truncate the log is that the Log Sequence Number (LSN) of the log chain is destroyed – with no indication that it’s happened. After running one of these BACKUP LOG WITH… statements, you would not be aware of the broken chain until a restoration of the log backups was attempted. Also, using BACKUP LOG WITH TRUNCATE_ONLY will set and leave the Autogrow property ON for the log file.

One of the simplest ways to truncate the log is to perform a normal log backup:

BACKUP LOG AdventureWorks TO DISK = 'c:\backups\advw20120317.trn'

This will not break the LSN chain, and will effectively truncate the inactive portion of the log, IF a checkpoint has been performed since the last log backup. A checkpoint is a committal to disk of active transactions in the log file. So what initiates a checkpoint? Checkpoints happen after certain database activities, but one way to make sure a checkpoint happens is to run a database backup (not a log backup).

Backing up the log in order to truncate it will not reduce the size of the physical log file, it will only clear inactive transactions, thus preventing further unnecessary growth of the log file.

If the situation is dire, however, with the log having grown so large that there is minimal operational disk space left, performing normal backups may not be possible. In this case you will need to reset the recovery model of the database to SIMPLE in order to truncate the log – which will break the LSN chain:

USE AdventureWorks
GO
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL
GO

After breaking the chain by setting the recovery model to SIMPLE, an attempted log backup will result in an error, even after resetting the recovery model to FULL:

BACKUP LOG AdventureWorks TO DISK = 'c:\backups\advw20120317_2.trn'
Msg 4214, Level 16, State 1, Line 2
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.

This error will result even if a database backup does exist – the LSN chain is broken, so the attempted BACKUP LOG statement cannot refer to the existing database backup.