SQL Server error 9002 means the transaction log is full. Query log_reuse_wait_desc in sys.databases to find the cause. Microsoft names a missing log backup as the top cause. Shrinking the log alone does not fix the problem.
Updates fail and applications show error 9002. SQL Server logs every edit and cannot reuse that space until the reason clears.
Steps follow the Microsoft guide to troubleshoot a full transaction log. T-SQL samples here are not executed in a test instance.
How do You Find Why the Log is Full?
SELECT name, log_reuse_wait, log_reuse_wait_desc
FROM sys.databases;That column names the reason SQL Server cannot reuse log space.
What does Each Wait Reason Mean?
| Value | Meaning and Response |
|---|---|
| LOG_BACKUP | The log is not backed up in FULL or BULK_LOGGED model. Take log backups. Microsoft calls this the top cause |
| ACTIVE_TRANSACTION | A long transaction is open. Find it with DBCC OPENTRAN then commit or roll it back |
| ACTIVE_BACKUP_OR_RESTORE | A backup or restore is in progress. Wait or cancel it |
| REPLICATION | Transactions are not yet delivered to the distribution database. Check the Log Reader Agent |
| DATABASE_MIRRORING | The mirror is paused or far behind. Fix the lag |
| CHECKPOINT | No checkpoint since the last truncation. Execute CHECKPOINT |
How do You Free Log Space With a Log Backup?
BACKUP LOG [SalesDb] TO DISK = 'D:\Backups\SalesDb_LOG.trn';When the log was never backed up Microsoft advises two log backups. Review the recovery model afterward.
What if the Disk or File Limit is the Cause?
- Full disk. Free space or move the log file to a disk with space. Never use a compressed file system. As a temporary option add a second log file on another disk.
- Size limit. Enable autogrow with a nonzero FILEGROWTH or raise MAXSIZE.
Microsoft states that shrinking the log does not fix a full log. Clear the wait reason first. When the database was in recovery use ALTER DATABASE SET ONLINE afterward.
For file basics see what an LDF file is and what Microsoft SQL Server is. See also a bad backup in how to check a corrupt BAK file.
Frequently Asked Questions
What is SQL Server Error 9002?
It reports that the transaction log of a database is full.
Which Cause Appears Most?
Missing log backups in the FULL or BULK_LOGGED recovery model according to Microsoft.
Does Shrinking the Log Fix Error 9002?
No. Microsoft states that you must clear the wait reason first.
How do I See the Reason?
Query log_reuse_wait_desc from sys.databases.
Can I Add Another Log File?
Yes. Microsoft lists a second log file on another disk as a temporary measure.
Key Takeaways
- Error 9002 means the transaction log is full.
- Wait reason values name the cause.
- Log backups clear that cause.
- Shrinking alone does not fix a full log.
- Check disk space and file limits too.
Sources and References
Steps follow the Microsoft guide.
- Troubleshoot a full transaction log, error 9002 (Microsoft Learn) learn.microsoft.com