SQL Server Interview Question #269

What is log truncation?

SQL Server Internals & Administration Senior Advanced

Detailed Explanation

Log truncation marks inactive portions of the transaction log as reusable. It does not normally reduce the physical size of the .ldf file.

Whether truncation can occur depends on the recovery model and whether anything still requires the log records. In FULL recovery, regular transaction log backups are generally required for normal log reuse after the log backup chain is established.

The sys.databases log_reuse_wait_desc column helps identify why log space cannot currently be reused.

Code Example

SELECT name,
       recovery_model_desc,
       log_reuse_wait_desc
FROM sys.databases;