Harry Potter and the Curious 'Log Full' message

[JJayUK]

Hello again,

Here's one!

SQL Server 7 with Service Pack 2.

The database is about a GIG and it's set to 'Truncate Log on Checkpoint'. The log is also set to automatic growth (at 10%).

Despite this we sometimes get messages sent to the event log saying something like "Error: 9002, Severity: 17, State: 2 The log file for database 'DBASE' is full. Back up the transaction log for the database to free up some log space. "

How can this happen when all the above options are set? Could it be because a user has executed a large query which has filled up the log before a checkpoint can be reached?

Any clues greatly appreciated! Regards,
JJayUK

 

[tlbroadbent]

Filling the user log is possible with large queries even if the "Auto Grow" and "Truncate Log on Checkpoint" options are set On. Truncating the log only removes inactive transactions. While a transaction is active (that is uncommitted) it cannot be truncated. I've found that it helpful to set the growth factor to a large fixed value rather than a percentage when large updates will be done (i.e., 50-150MB).

Another possibility is that the disk is running out of space and the log and database cannot grow any larger.

I highly discourage use of "Truncate Log on Checkpoint" on production databases. Doing so inhibits the ability to backup and restore transaction logs. This makes "point in time" restores impossible. It also insures that transactions will be lost between the time of the last full backup and the time of a restore. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.