You check your disk space and see it's at 0%
Your SQL Server database log file — that .ldf file — has grown to 200GB. Your apps are crashing. You're panicking. I know the feeling. Here's what to do right now.
The real fix: backup the log, then shrink it
First, check your database recovery model. If it's FULL, the log won't shrink unless you back it up. Here's the direct command:
-- Backup the transaction log (change MyDatabase to your DB name)
BACKUP LOG MyDatabase TO DISK = 'C:\backup\MyDatabase_log_backup.bak'
-- Then shrink the log file
DBCC SHRINKFILE (MyDatabase_log, 100) -- shrinks to 100 MBRun these in SQL Server Management Studio (SSMS) or sqlcmd. This worked for me on SQL Server 2019 and 2022. The log file drops from hundreds of GB to a few MB instantly.
What if backup log doesn't work?
Sometimes the log is full and the backup itself fails. In that case, switch to SIMPLE recovery model, then shrink:
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
DBCC SHRINKFILE (MyDatabase_log, 100)
ALTER DATABASE MyDatabase SET RECOVERY FULLBe careful: switching to SIMPLE breaks your log backup chain. You'll need a full backup right after. But it's fast and works every time.
Why the log file grows so fast
SQL Server logs every transaction — inserts, updates, deletes — in the log file. When you run a big update that affects millions of rows, the log grows to record every change. If you're in FULL recovery model and never backup the log, it never gets a chance to truncate (free up space for reuse). The file just grows and grows.
Common triggers:
- Large DELETE or UPDATE operations on big tables
- Index rebuilds on large tables
- ETL processes that insert millions of rows
- Replication or CDC (Change Data Capture) logging
Less common variations of the same issue
1. Log file grows even after backup
If you backup the log but the file doesn't shrink, check for long-running open transactions. Run this:
DBCC OPENTRANIf you see a transaction that's been open for hours, kill it with KILL {spid} (but be careful — it might roll back for a long time).
2. Log file is on a separate disk that fills up
Your data files (.mdf) might be fine, but your log disk is full. Move the log to a bigger drive:
ALTER DATABASE MyDatabase MODIFY FILE (NAME = MyDatabase_log, FILENAME = 'E:\Logs\MyDatabase_log.ldf')Then stop SQL Server, copy the file, restart. This saved me when a RAID disk failed.
3. VLFs (Virtual Log Files) are too many
When you grow the log file in tiny increments (like 1 MB at a time), you create thousands of small VLFs. This slows performance and makes shrinking harder. Check with:
DBCC LOGINFOIf you see more than 1000 VLFs, shrink the log and regrow it in bigger chunks (like 1 GB at a time).
Prevention: stop it from happening again
Don't just shrink — fix the root cause. Here's my checklist:
- Schedule log backups every 15-30 minutes for busy databases. Use SQL Server Agent or a maintenance plan. This keeps the log small automatically.
- Set log file autogrowth to a fixed size, not percent. For example, grow by 500 MB or 1 GB. Never use "by percent" — it grows faster as the file gets bigger.
- Monitor log file size with a simple query:
SELECT name, size * 8 / 1024 AS SizeMB
FROM sys.database_files
WHERE type_desc = 'LOG'Set up an alert when log size exceeds 80% of its drive.
If your database is a reporting database that doesn't need point-in-time recovery, switch to SIMPLE recovery model. Best change I ever made for a warehouse.
When to call for backup
If the log file is over 500GB and you can't shrink it even with SIMPLE recovery, you might have corruption. Run DBCC CHECKDB first. If it finds errors, restore from backup. Don't wait — call your DBA.
"I had a log file hit 1TB once. Simple recovery + shrink dropped it to 100MB. But I also lost a day of backups. Always test on a copy first."
You're not alone. Every DBA has this story. Fix the growth, then fix the process. Your disk will thank you.