Log truncation marks transaction-log portions for reuse after they are no longer needed. A common mistake is choosing SIMPLE recovery for every production OLTP database merely because the log might fill the disk. With FULL recovery, regular transaction-log backups normally prevent continuous growth; if a backup does not truncate the log, investigate the reuse blocker instead of changing recovery models. section 12.7
Check the reason before prescribing a fix:
- Run the log-reuse diagnostic query and look for transient blockers such as a missing checkpoint, an active backup or restore, or a database snapshot. section 12.7
- Investigate repeated ACTIVE_TRANSACTION waits: long-running transactions can grow the log and also contribute to lock contention or deadlocks. section 12.7
- Treat persistent REPLICATION or AVAILABILITY_REPLICA waits as signs that transactions are still being applied to secondary databases. section 12.7
Listing 12.6 Exploring the reason the log was not truncated
SELECT
name
, log_reuse_wait_desc
FROM sys.databases ;
Use the sys.databases query to identify why the previous truncation attempt did not release log space. section 12.7
