August 6, 2003 at 3:12 pm
The following error if from a from a job that calls a stored procedure to defragment the database.
The log file for database 'ION_Data' is full. Back up the transaction log for the database to free up some log space. [SQLSTATE 42000] (Error 9002) Executing DBCC INDEXDEFRAG (0, EventString,2) - fragmentation currently 31% [SQLSTATE 01000] (Error 0). The step failed.
The transaction log is set to unrestricted size and growth by 10%. BOL states..
"the defragmentation is always fully logged, regardless of the database recovery model setting. The defragmentation of a very fragmented index can generate more log than even a fully logged index creation. The defragmentation, however, is performed as a series of short transactions and thus does not require a large log if log backups are taken frequently or if the recovery model setting is SIMPLE."
The recovery modele is SIMPLE. Is it possible that DBCC INDEXDEFRAG is looking for more than 50Meg of space at a time?
(10% growth and 500Meg file). Any insight would be welcome.
Thank you
Tom Hughes
August 6, 2003 at 3:58 pm
Remember in the "old" days (6.5) you need to have at least the equivalent of the largest table(bytes wise) free and plus 10% -> 20% before starting to drop and recreate indexes.
According to your BOL quote, since its fully logged it seems to be valid. What is the size of the database, largest table size and free space on log drive?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply