Fix SQL Server Error 9001: Database log is full
The database log is full—here's how to free space and stop it from happening again. No fluff.
Quick answer for advanced users
Run DBCC SHRINKFILE (YourLogFile, TRUNCATEONLY) after backing up the log, or switch recovery model to Simple and shrink. But don't stop there—find what's filling it.
What's error 9001 and why does it happen?
Error 9001 in SQL Server means the transaction log (the .ldf file) has run out of space. I've seen this hit at the worst times—midnight backups, right before a payroll run. A client of mine had a full log during a bulk insert of sales data from an ERP system; locked up the whole database. The log fills when transactions aren't being checkpointed or backed up properly, or when a long-running transaction holds onto old log records.
Step-by-step fix
1. Check log file size and free space
Run this query to see what's going on:
SELECT name, size/128.0 AS SizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeMB
FROM sys.database_files WHERE type_desc = 'LOG';If FreeMB is tiny (or zero), the log is stuffed.
2. Back up the transaction log
If you're in Full recovery model, backing up the log marks the inactive portion for reuse. Do this:
BACKUP LOG [YourDatabase] TO DISK = 'C:\Backups\YourDatabase_log_backup.bak';Then check free space again.
3. Shrink the log file
After the backup, shrink it safely:
DBCC SHRINKFILE (YourDatabase_log, 100); -- target size in MBOr if you just want to dump inactive space:
DBCC SHRINKFILE (YourDatabase_log, TRUNCATEONLY);4. Switch to Simple recovery model (if acceptable)
If point-in-time recovery isn't critical, set it to Simple, then shrink:
ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE;
DBCC SHRINKFILE (YourDatabase_log, 100);Then switch back to Full if needed (and take a full backup immediately after).
5. Find the culprit transaction
Use this to see what's holding the log hostage:
SELECT session_id, command, wait_type, wait_time, blocking_session_id
FROM sys.dm_exec_requests WHERE session_id > 50;Kill the culprit with KILL session_id only if you're sure—it'll roll back, which could take a while.
Alternative fixes if the main one fails
Add more log space: Just increase the file size or add another log file on a different drive. Not ideal but gets you running fast.
Use DBCC CLEANTABLE: If the log is full because of a large delete operation, DBCC CLEANTABLE can help free space after the operation completes—but only if you've already backed up the log.
Restart SQL Server (last resort): Only do this if you can't back up or shrink and the database is unresponsive. It'll force a checkpoint and may free log space, but you risk losing transactions. I've done this once—never again if I can avoid it.
Prevention tip
Set up regular log backups. For databases in Full recovery, schedule log backups every 15–30 minutes (or as often as your RPO allows). Also set a max file size for the log file in SQL Server properties—so it throws an error instead of filling the drive. And monitor log reuse wait info via sys.dm_db_log_space_usage weekly. A client of mine with a busy e-commerce site used to hit 9001 every month until I automated log backups with Ola Hallengren's scripts. Hasn't happened since.
Was this solution helpful?