A Primer on Log Shrinking and Truncation in SQL Server

Shrinking the Log

To shrink the physical log file, you can use one of the DBCC Maintenance Statements – DBCC SHRINKFILE. This statement can be run under either database recovery model. The following script will truncate the log by setting the recovery model to SIMPLE, shrink the log to 10 MB using DBCC SHRINKFILE, and then reset the recovery model to FULL:

USE AdventureWorks
GO
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (AdventureWorks_Log, 10)
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL
GO
Again, be aware that the LSN chain is immediately broken when the recovery model is set to SIMPLE.

Restarting the Chain

To restart the LSN log chain, immediately perform a full or differential backup after resetting the recovery model to FULL, and then resume normal log backups:

BACKUP DATABASE AdventureWorks TO DISK = 'c:\backups\advw20120317_new.bak'
GO
BACKUP LOG AdventureWorks TO DISK = 'c:\backups\advw20120317_new.trn'
A successful backup of the log will confirm the integrity of the LSN chain. It is essential to get the log chain started again as soon as possible to ensure point-in-time recovery.

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 |