From 100% disk to 14% — fixing SQL Server log bloat
A SQL Server transaction log quietly grew until it consumed an entire disk volume. No alerts, no gradual warning — just a message saying disk usage had crossed 90%. This is how we diagnosed it, why our first fix failed, and the decision that brought it down to 14% and kept it there.
A SQL Server transaction log quietly grew until it consumed an entire disk volume. No alerts, no gradual warning — just a message from the person monitoring the server saying disk usage had crossed 90%. This is how we diagnosed it, why our first fix failed, and the decision that brought it down to 14% and kept it there.
How it was caught
We have someone who monitors the server regularly — checking snapshots, reviewing general system health. During a routine check, he noticed disk usage had climbed above 90% and flagged it immediately. His first instinct was that either the system snapshots or a log file without rotation was the culprit. Before doing anything, he reached out to consult on next steps — exactly the right call.
He ran a search for the largest files on the server and found a large file inside the Docker volume belonging to the SQL Server container. That narrowed it down fast.
The root cause: Full recovery model without backups
SQL Server databases operate under one of two main recovery models: Full or Simple. The difference matters a lot for transaction log behavior.
| Full recovery | Simple recovery | |
|---|---|---|
| Log behavior | Keeps all transactions until a log backup is taken | Automatically reclaims log space after each checkpoint |
| Point-in-time restore | Yes — restore to any moment in time | No — restore to last full backup only |
| Requires | Regular log backups to prevent unbounded growth | Regular full backups |
Our database was on Full recovery — but we had no scheduled log backups running. This is the exact scenario SQL Server warns against: Full recovery without log backups means the transaction log grows indefinitely, because SQL Server has no checkpoint to safely truncate it against.
Note: Full recovery is not inherently wrong — it's the right choice when point-in-time restore matters. But it requires log backups to function correctly. Without them, the log file will grow without bound until it fills the disk.
Why our first fix failed
The obvious first attempt was to shrink the log file directly using DBCC SHRINKFILE. It didn't work. The reason: in Full recovery mode, SQL Server won't release log space until a log backup has been taken — there's nothing to truncate against. The log was holding onto all that space because it had to, by design.
The second instinct was to take a full backup immediately to unlock the shrink. But the disk was already above 90% — a full backup includes the transaction log, and there simply wasn't enough free space to write it. We were stuck.
The catch-22: Full recovery without backups means when you finally need to fix it, the disk is full, so you can't take a full backup, so you can't shrink the log. You have to break the cycle a different way.
The escape: data-only backup first
Before touching the recovery model, we needed a safety net. A full backup — which includes the transaction log — was too large to fit on the remaining disk space. But a data-only backup skips the log entirely, making it small enough to write even with limited space.
-- Backup data only, no log
BACKUP DATABASE [YourDatabase]
TO DISK = N'/var/opt/mssql/backups/db_dataonly.bak'
WITH COPY_ONLY;
With that copy secured, we had a restore point if anything went wrong during the recovery model switch. The data was safe. Only then did we move forward.
The decision: switch to Simple recovery
After researching the options carefully — including the risks of changing recovery models on a live database — we decided to switch to Simple recovery. This was not a default choice. It was a deliberate trade-off based on what we actually needed.
Point-in-time restore sounded valuable in theory, but in practice we already had two recovery mechanisms in place: regular full database backups and server-level snapshots. The ability to restore to an arbitrary moment in time — which is what Full recovery uniquely provides — wasn't something we'd realistically use given our setup.
Switching to Simple recovery immediately allowed SQL Server to reclaim log space at the next checkpoint:
-- Switch to Simple recovery
ALTER DATABASE [YourDatabase]
SET RECOVERY SIMPLE;
GO
-- Shrink the log file
DBCC SHRINKFILE (YourDatabase_log, 1);
GO
Disk usage dropped from above 90% to around 14%.
Immediately after, we took a clean full backup to confirm the new setup was working correctly before relying on the automated jobs.
Making sure it never happens again
Switching recovery models solved the immediate crisis, but we needed a permanent maintenance plan. We set up automated backups using SQL Server Agent Jobs — full database backups running every 3 hours. This means we have frequent restore points without relying on transaction log chains.
-- SQL Server Agent Job (simplified)
-- Runs every 3 hours
BACKUP DATABASE [YourDatabase]
TO DISK = N'/var/opt/mssql/backups/db_backup.bak'
WITH FORMAT, INIT, COMPRESSION;
Rule of thumb: If you're on Full recovery, schedule log backups — not just full backups. If you're on Simple recovery, schedule frequent full backups. Either way, the log needs somewhere to go.
Results
Disk usage — before
>90%
and climbing
Disk usage — after
14%
stable ever since
Key takeaways
Full recovery without log backups is a time bomb. The log will grow indefinitely. If you're on Full recovery, scheduled log backups are not optional — they're what makes the model work.
DBCC SHRINKFILE won't help if you haven't backed up first. In Full recovery, log space can't be reclaimed until a log backup checkpoint exists. If the disk is already full, you're in a catch-22.
A data-only backup can break the deadlock. When the disk is too full for a full backup, a COPY_ONLY backup without the log is small enough to fit — and gives you a restore point before making any risky changes.
Recovery model is a deliberate decision, not a default. Full recovery is right when point-in-time restore is a real operational requirement. If you already have full backups and snapshots covering your restore needs, Simple recovery is often the more honest choice.
Automate backups before you need them. SQL Server Agent Jobs are straightforward to set up. The 3-hour backup cadence we landed on gives us frequent restore points without the log bloat risk.