SQL Server (2005/2008): Does full backup truncate the log in full recovery mode

Better Stack Team
Updated on January 29, 2024

In SQL Server, when a database is in the Full Recovery Mode, taking a full backup does not automatically truncate the transaction log. The transaction log is a critical component of SQL Server's data recovery and consistency model. It records all changes to the database and must be managed separately from the full backups to maintain its size and prevent it from growing indefinitely.

Here's how the process works in Full Recovery Mode:

  1. Full Backup - A full database backup in Full Recovery Mode backs up all the data in the database, including the transaction log. However, it does not truncate the transaction log.
  2. Log Backup - To truncate the transaction log and free up space, you need to take regular transaction log backups. Log backups capture all the transactions that have occurred since the last log backup, allowing the transaction log to be truncated and reused. This is an essential part of maintaining a manageable transaction log file.
  3. Checkpoints - In SQL Server, there are automatic checkpoints that occur, which write dirty pages to the data files and allow for the truncation of the transaction log. These checkpoints can help keep the transaction log from growing excessively.
  4. Manual Shrinking - While it's generally not recommended, you can manually shrink the transaction log file using the DBCC SHRINKFILE command. However, this should be used cautiously, as it can lead to fragmentation and performance issues.

Taking regular log backups is the recommended approach for managing the transaction log in Full Recovery Mode. The frequency of log backups will depend on your specific requirements and the volume of transactions in your database. By regularly backing up the transaction log, you ensure that it doesn't grow uncontrollably, and you maintain a structured log chain that allows for point-in-time recovery.

Failure to manage the transaction log properly in Full Recovery Mode can result in the log file growing excessively and potentially filling up the disk, causing database outages.

Got an article suggestion? Let us know
Explore more
Licensed under CC-BY-NC-SA

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Make your mark

Join the writer's program

Are you a developer and love writing and sharing your knowledge with the world? Join our guest writing program and get paid for writing amazing technical guides. We'll get them to the right readers that will appreciate them.

Write for us
Writer of the month
Marin Bezhanov
Marin is a software engineer and architect with a broad range of experience working...
Build on top of Better Stack

Write a script, app or project on top of Better Stack and share it with the world. Make a public repository and share it with us at our email.


or submit a pull request and help us build better products for everyone.

See the full list of amazing projects on github