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.

Pages: 1 2




Related Articles :

  • No Related Articles Found

6 Responses to “A Primer on Log Shrinking and Truncation in SQL Server”

  1. Christian Tarnutzer Reply April 24, 2012 at 10:16 am

    Nice and small Primer, Seth!
    Still, there could be an addition: Why not use TRUNCATEONLY in the DBCC command? It truncates just back to the point of the last log backup (or the given size, if that’s reached before) and does not need a switch to SIMPLE recovery, thus not breaking the LSN chain:

    USE AdventureWorks
    GO
    DBCC SHRINKFILE (AdventureWorks_Log, 10, TRUNCATEONLY)

    Important is to run this command after a log backup and best after a checkpoint. Like this you are sure that the log contains as less virtual log files as possible. But be aware that if you truncate the log too much, it will have to grow right after this operation. And logfile growth in FULL recovery needs time and therefore slows down transactions on the database

  2. Thanks for the comment, Christian. The DBCC SHRINKFILE TRUNCATEONLY option targets the data file only, not the log file. It does not apply if you are using DBCC SHRINKFILE on a transaction log file. The MSDN Library says:

    TRUNCATEONLY
    Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
    TRUNCATEONLY is applicable only to data files.

    http://msdn.microsoft.com/en-us/library/ms189493.aspx

  3. I use the UI to perform a shrink on the transaction file, then backup the transaction file, then perform the shrink again. There is no need to change your recovery model and thus break the LSN chain. I don’t remember the specifics, but the shrink operation needs to be run twice because the first time it was run, if the new entries are being written to the log at the end of the file, not all unused space can be physically removed. However, if the shrink operation detects this, it will fill up the rest of the log file with null space (or reset the pointer). Then when a transaction backup happens, the virtual space at the end of the log file is removed. Durning the second shrink, the log file will physically shrink to its smallest size. Then you can let it grow as needed, but hopefully max out at a smaller size.

  4. 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….?

    When database is in simple recovery mode database backup does not truncate the log.
    Only implicit or explicit checkpoint will truncate the log.

    When checkpoint is issue during full database backup and database has simple recovery mode there is no truncation of transaction log

    • Hi Krystian,
      Thanks for the input. While you are correct about the fact that implicit and explicit checkpoints do truncate the inactive portion of the log file, I find that this MSDN article also suggests that a data backup will truncate the log as well:

      “After switching [to the simple recovery model], you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log.”

      http://msdn.microsoft.com/en-us/library/ms178052%28v=sql.105%29.aspx

  5. Hi Krystian,
    Thanks for the input. While you are correct about the fact that implicit and explicit checkpoints do truncate the inactive portion of the log file, I find that this MSDN article also suggests that a data backup will truncate the log as well:

    “After switching [to the simple recovery model], you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log.”

    http://msdn.microsoft.com/en-us/library/ms178052%28v=sql.105%29.aspx

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |