What is Transaction Log Truncation and When Does it Occur?

Transaction log truncation is a process that frees up space in a SQL Server database's log file for reuse. Learn what it is and when it occurs.

What is Transaction Log Truncation and When Does it Occur?

Transaction log truncation is a process that frees up space in the log file for reuse. It occurs automatically at regular checkpoints if the SQL Server database uses the simple recovery model. If the database uses the full or bulk record retrieval model, truncation occurs automatically after each log backup. With this option selected, Veeam Backup & Replication produces a backup file or VM replica and does not trigger truncation of the transaction log.The active part of the log contains all log records, which are more recent than the oldest open transaction.

When the record is truncated, the VLFs, which do not contain the active part of the record, are released for reuse. The active part of the log is left unchanged, not truncated. Truncation only releases entire virtual log files for reuse and not part of them.If you truncate transaction logs with Veeam Backup & Replication, the transaction log chain will be broken and the Microsoft SQL Server log backup job will not be able to produce a consistent log backup. Remember that only the transaction log backup, but NOT the full database backup, will truncate the transaction logs in the transaction log file and make it available for reuse.

However, the size of the transaction log file will not decrease, since the truncated space will not be deallocated.When you configure the database with the simple recovery model, the SQL Server transaction log will be marked as inactive and automatically truncated after the active transaction is committed. Unless there is some kind of unexpected delay, log truncation will occur automatically after a checkpoint (if the database is in the SIMPLE recovery model) or after a log backup (if the database is in the FULL or BULK RECORD recovery model).You can tell Veeam Backup & Replication to truncate logs after a backup or VM replica has been successfully created. Because transaction logs on the VM are truncated, you cannot use them to get the restored database sometime between worksessions.One thing I see many administrators asking is how to determine when a transaction log will be truncated and how to manage its size. When a database is configured with a full recovery model, SQL Server needs to use a transaction log backup to trigger truncation of inactive virtual log files (VLFs) from SQL Server transaction logs.

Otherwise, it will continuously grow until it runs out of space.You can learn what prevents truncation of your log by querying system's log_reuse_wait and log_reuse_wait_desc columns. This way you can ensure that your SQL Server transaction logs are properly managed and that your databases are always up-to-date.

Charlotte Wilson
Charlotte Wilson

Friendly travel advocate. Freelance zombie scholar. Extreme web practitioner. Evil coffee buff. Professional beer practitioner.

Leave Reply

Required fields are marked *