Optimization job fails with 9002 after tran log fills up drive

[jbradleyharris]

I have an optimization job on a SQL 2000 server on Windows Server 2003 that continually fails with the message:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 9002: [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'wfcdb' is full. Back up the transaction log for the database to free up some log space.

The job is configured to reorganize data and index pages with the original amount of free space, then remove unused space from the files. As the job runs the tran log grows until it completely fills the drive and the job fails. Then SQL freezes until I manually backup the tran log and shrink the the file using DBCC SHRINKFILE(logfile,size). I have an integrity check job on a different server that does the same thing.

I am a novice at SQL administration and was thrown in at the deep end when our DBA left. Any assistance in troubleshooting this problem would be greatly appreciated.

 

[SQLBill]

First thing to check is what Recovery Mode is the database set for? Right click on the database name, select Properties, then Options. If the database is set for FULL mode, the log file won't shrink until a Transaction Log backup is made. If it is set for SIMPLE mode, it should be clearing the log as transactions are committed.

Some actions won't clear until the job is complete. This happens with reindexing and other things. It needs to keep a complete copy of how the index (or whatever) was in case a rollback is needed. If you are rebuilding the indexes, check to see if you are doing all of them at once. If so, do a couple at a time.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[jbradleyharris]

The recovery mode is Full.

Other jobs that are set up for this database are an integrity check, which runs before the optimization, a database backup that runs after the optimization, and a series of tran log backups set to run every 3 hours from 7:00 AM to 11:00 PM.

 

[SQLBill]

Your options, as I see them, are:

1. change the recovery mode to SIMPLE, then do the optimizations, then change it back before backups begin.

2. have a transaction log backup run before the optimizations begin.

I would suggest #2 as the best option. The tlog backup would have to finish before the optimizations begin.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[jbradleyharris]

I chose option #1. The tran logs are backed up multiple times throughout the day so in that sense they are always backed up before the optimization job runs and clearly it wasn't helping. The optimization job completed successfully last night. I've set the recovery model back to Full. Now we'll have to see if it will work again.

 

[mrdenny]

If you select #1 then your transaction log sequence is broken. If your log is growing to large you need to backup the log more often.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog