Our LDF file hit 400GB against a 60GB data file and the drive is nearly full. I shrank it once and it grew right back within a week. What actually stops this?
SQL Server transaction log file growing out of control
Solved SQL & Databases
BA
Barry Allen July 24, 2019
2 replies
8,140 views
Reviewed by moderators
A log that regrows after shrinking means nothing is truncating it. Almost always one of these:
1
Full recovery model with no log backups. Check with
SELECT name, recovery_model_desc FROM sys.databases;. If you need point in time restore, schedule log backups every 15 to 30 minutes. If you do not, switch to Simple.2
Something holding the log active: run
SELECT log_reuse_wait_desc FROM sys.databases WHERE name='yourdb';. REPLICATION or ACTIVE_TRANSACTION there points at the real culprit.3
Only after fixing the cause, shrink once to a sensible size and leave it alone. Repeated shrinking fragments the log.
It was full recovery with zero log backups, classic. Backups scheduled, log holding at 8GB now. Solved.