SQL Transaction Log Growth

[AKPolarBear]

We are on 7.6.100a and SQL 2000.

I am having a problem with transaction logs growing larger than the database itself. These logs grow enough that disk space is being used up and causing grief.

We back up data nightly and run transaction logs every hour between 7 am and 9:30 pm. The 7 am trx log can be huge for some reason almost every time. We have the bak set to full, we delete trx logs every 1 day, we shrink the dB at 50mb but this is somewhat mute as the dB is about 7 gig.

We optimize daily at 10pm, integrity at 10:20pm, backup at 10:30pm. There is plenty of time between operations so no overlap. We have a total of 4 Macola companies or databases that we use. I have the same growth problem with all the databases. One is the main data and the others are duplicates that are used for specific functions but only one of those gets any active Macola transactions put into it and those are very limited, once or twice a week by a single user. Their maintenance plans are similar or set for weekly. The schedules are mapped out so to avoid conflicts. Tape to backups are also performed during the night but that should not be a factor.
I suppose I could stop the maintenance plan on 2 of those dB's to conserve the space but that does not solve the problem.

 

[NEmacGuy]

Are you backing up to tape using Backupexec ?
I saw something like you are describing. We had a SQL expert then and he took care of the issue. If I remember correctly he said that the Backup itself was passing all of the data in the DB thru the log file every night.
Not sure what was done to change it.

 

[AKPolarBear]

Actually we are using Backup Exec. We copy to disk first then to tape.

I will have to look into that.

I knew when Symantec bought them there would be trouble!

 

[kscheeler]

Turn off optimization. Since you are using logging, the system is logging everything that the optimization is doing which is moving around data pages. That's why your first trx log of the day is about the same size as the database. I've had it on at a few places but haven't seen an improvement over not using it.

On your log file growth. I would first back up the database, set the recovery model of the database to Simple, then shrink the log file to your desired size, then reset the recovery model to Full.

Kevin Scheeler

 

[stravis]

Everyone else makes great points on how to decrease your log file growth. One other method is to increase the frequency of log file backups. Large corporations backup logs every 2-3 seconds on Terabyte sized databases. My point being is that many administrators are terrified of backing up the log to often. I would set it to a more reasonable schedule like every 3-5 minutes. This will create a huge number of log backup files, but the system handles these and a restore through Enterprise Manager of data will not care how many files it needs to deal with. If you perform SQL recoveries by scripting then you would have your work cut out for you.

Scott Travis

http://www.infospring.net

 

[AKPolarBear]

So far, this is what I am changing to see how it all works out.

I have 2 "live" Macola databases. One is the main data and the other has a limited use. Optimization and Integrity checks will be run once per week instead of daily with a possibility of changing one or both to once per month. Transaction logs, now set to run every hour between 7am and 9pm will be set to every hour, 24 hours per day. Depending on how that works, I might go to every 1/2 hour.

My other databases which are just snapshots of a year end capture and have no active data input, will be changed to simple backups once per day, no optimizations or integrity checks.

I am still trying to figure out the BackupExec issue.

Thanks for all the suggestions.