Maint Plan Caused TLOG to Fill

hi experts,

This is 2005 SP2

My Maint plan spun out of control and the log became full before the m plan completed.

The db data size is 8 GB and the max size for the log is 8 GB. It was 2 GB before the maint plan started executing. So it wrote 6 GB to the log. That's a lot of logging for a small database.

The plan reindexes tables , views and adds 10 % free space to tables. Then it updates statistics. It has been running flawlessly for 2 years.

Any idea as to what happened? What should I check in the database?

Thanks,
John

If your database is full recovery model , schedule backup transaction log periodically. That will free your log files.
Are you using log shipping or transaction replication?

Run "DBCC SQLPERF (LOGSPACE) " to check what percentage of the transaction log is currently full. If its a low % then its tricky to know what caused it to fill up before. You can at least then shrink the log for now using "DBCC SHRINKFILE (<log file name>, 2000, TRUNCATEONLY)"
If the log file is still 80% to 100% full then perhaps try a few things below:
What recovery model is on the database ? If its FULL recovery, then do you have regular transaction log backups and are they running correctly as scheduled ?
Maintenance plans do not typically use much log space. Are you certain that no other large data import or large update could have filled the transaction log on that day ? Perhaps run "DBCC OPENTRAN" to check for open transactions that could be causing the log file to fill.
When last did you run "DBCC CHECKDB" ? An integrity problem in the data file can also prevent a checkpoint, which would grow the log file.

I am not using replication or log shipping. Yes database is in FULL recovery mode. Logs are backed up every 2 hours.

Thanks DavidFarr for your excellent suggestions. I have already truncated the log so by doing that I have 'destroyed the evidence' but I will use your tips if it happens again.

No I've not been running DBCC CHECKDB but will start doing that weekly.

Thanks much. John - Memphis TN USA

Hi..
I think you should try below.
1. Lockout the DB as you're doing reindexing
2. Change the recover model to simple.
3. Run your job.
4. Once job has finished take immediate full backup.
5. Change the recovery model back to full
6. change the database back to normal state.
You might need to disable the tlog backup job if you're taking the tlog backups 24X7.
Tell me if this helps...!!!!

Thanks Ram.

Unfortunately, I cannot lock-out the database. It is used 24 x 7.

You may have a point though with the T-Log backup job which runs every 2 hours. It ran once while the maint plan was executing. It may be getting in the way so I will change the schedule to not allow it to run during this time.

Thak you. John

Considerations for Switching from the Simple Recovery Model to Full

If you switch from the full recovery model to the simple recovery model, you break the backup log chain.
Therefore, it is strongly recommend that you back up the log immediately before switching, which allows you to recover the database up to that point.
Then, you can backup full /or defferntial and to truncate the inactive portion of the transaction

Here is an update on my situation.

Yes, I do run regular T-Log backups - every 2 hours.

I recently re-ran the maint plan again after disabling the TLOG Backup job. The results were the same. The tlog still became full while the plan was executing. I had to terminate the maint plan, then shrink the log file. (It is worth noting that I am running the maint plan while the system is in use, but it is a time of less use than normal.)

Now, many indexes are heavily fragmented and more tables scans are occuring than were weeks ago when the maint plan was running correctly.

2 questions:

1. What could be causing the maint plan to fill the log - an 8 GB log for a datafile size of 8 GB seems way too large ?

2. Will I have to get all users out for a few hours, then run the maint plan to get my tables back in good working order - is that my best option at this point?

I welcome all ideas. Thanks, John