Context
SQL Server must retain transaction-log records until the relevant recovery and replication obligations are satisfied. A growing log may therefore reflect a missing log backup, an open transaction, or a lagging availability replica. The reported reuse wait helps locate that obligation. Physical file size alone cannot tell you which condition is responsible.
Prerequisites
Gather SQL Server version, database recovery model, log file layout, free disk space, backup history, active maintenance, and availability or replication configuration. Obtain authorized access to catalog and diagnostic views. Identify the recovery owner before making changes that affect the log chain. Do not delete or rename a transaction-log file, and do not switch recovery models as an exploratory space-saving step.
Follow the retention dependency
- Read
SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases;and focus on the affected database. Compare the result with occupied log space using supported diagnostics. Capture repeated samples because some waits are transient. Separate a large but mostly reusable file from one whose active contents are approaching capacity. - If the wait points to log backup, check the last successful applicable log backup, its artifact, and the backup job's identity and destination. A successful full database backup is not a replacement for the required log-backup sequence under the full recovery model. Repair the scheduled log-backup path with the backup owner rather than creating an untracked file that recovery operators cannot find.
- If an active transaction is retaining history, identify its owner and duration. Check whether it is legitimate batch work, an abandoned application transaction, or an administrative operation. Coordinate a normal commit or rollback where possible. Forcing termination can involve lengthy rollback and business consequences, so it requires a deliberate incident decision, not an automatic response to a large file.
- If the reason names an availability or replication dependency, inspect that consumer's delivery and hardening progress. Repair connectivity or processing at the dependency. Review storage headroom while it catches up. Temporary capacity can preserve service during diagnosis, but the retention cause still needs correction before growth becomes predictable.
Diagnostic table
| Reuse wait or observation | Investigation | Correction focus |
|---|---|---|
| LOG_BACKUP | Log-backup success and location | Restore the backup schedule |
| ACTIVE_TRANSACTION | Oldest relevant transaction | Resolve its owning workflow |
| AVAILABILITY_REPLICA | Replica delivery and hardening | Repair replication progress |
| File large, occupied percentage low | Capacity versus active use | Review steady-state sizing |
Illustrative scenario
A database's full backup succeeds every night, but its log-backup destination becomes inaccessible. The transaction log grows and reports LOG_BACKUP. The team restores the log-backup job's destination access, verifies the saved artifact, and confirms reusable space increases. It retains the appropriately sized file rather than repeatedly shrinking it and forcing expensive regrowth.
Correct and verify
Confirm that the reported dependency clears or changes as expected, occupied space stabilizes, and the application continues operating. For backup repairs, verify the recovery sequence in an isolated environment. For transaction repairs, inspect retry and reconciliation outcomes. Truncation makes internal log space reusable; it does not necessarily reduce the filesystem's allocated file size.
Operating procedure
Monitor used log space, disk headroom, reuse waits, growth events, and backup or replica progress together. Include the reason and database identity in alerts. Review sizing after large batch changes and preserve the recovery implications of every configuration change in the SOP so space management does not silently damage the intended restore path.
Explore Clouvenix database administration for implementation and operational handover.