SQL Server Performance Forum – Threads Archive

Log file is full

I’m getting the Msg 9002: The log file for database is full. Back up the transaction log for the database to free up some log space. The log file size has grown to 22G and there is only about 2G free space left on the hard drive. I guess I should issue a Backup log with no_log.

You can shrink log file.
What recovery model do you have?, because if it is full, then you have to backup transaction log frecuently and truncate log to reduce log size.
If you don’t backup transaction log as part of backup plan frecuently (I mean: full backup once a day) then you don’t need full recovery model. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The recovery model is full.

If you aren’t doing transaction log backups then you need to set it to simple. This should fix your issue. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Otherwise use DBCC SHRINKFILE to shrink the Transaction log size, and decide whether to keep Transaction log backups, if so schedule in regular intervals or follow as Derrick referred.
But ensure the full backups are maintained if SIMPLE recovery model is followed. On above BOL refers error 9002:

The user action that is appropriate to you depends on your situation. Potentially, possible actions include:
Backing up the transaction log
Freeing disk space
Moving the log file to a disk drive with sufficient space
Adding or enlarging a log file
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>