SQL Server transaction log growth: diagnosis before truncation

A transaction log that keeps growing has a reason, and SQL Server will tell you what it is. Shrinking the file without addressing the reason gives you a day of relief and the same page next week.

Ask the server why

SELECT name,       recovery_model_desc,       log_reuse_wait_descFROM   sys.databasesWHERE  name = 'YourDatabase';

Interpreting log_reuse_wait_desc

• LOG_BACKUP — full recovery model with no log backups running. Either schedule log backups or switch to SIMPLE if point-in-time recovery is not required.
• ACTIVE_TRANSACTION — a long-running or orphaned transaction is pinning the log. Find it with DBCC OPENTRAN.
• REPLICATION / AVAILABILITY_REPLICA — a replica or subscriber is behind; fix the lag, not the log.
• NOTHING — the log is reusable; the file is just big from a past event and can now be shrunk once.

The recovery model decision

FULL recovery without log backups is the classic misconfiguration: you pay the log growth cost and get no point-in-time capability, because the chain is never being captured. Decide per database: if the RPO tolerates losing everything since the last full/diff backup, use SIMPLE. If not, use FULL and schedule log backups at an interval matching the RPO.

-- Once the blocker is resolved, a single shrink is acceptable:ALTER DATABASE YourDatabase SET RECOVERY SIMPLE;  -- only if the RPO decision says soDBCC SHRINKFILE (YourDatabase_log, 4096);          -- target size in MB, leave headroom