Need help setting Transaction Log properties in SQL Server 2000

40 pts.
Tags:
backups
SQL Server 2000
Transaction logs
I'd like some help determining what would be the best settings for my transaction log properties.

When our SQL Server 2000 SP4 Database was setup it was done by developers without detailed information regarding all of the possible settings so most settings were left at their defaults.

I have been looking at the issues around setting the Transaction Log File Growth properties. The default is to grow automatically and to grow by 10% percent and the maximum File Size is set for unrestricted growth.That is our current setup. Our transaction log is 19Gb with a 10Mb data file so something needs addressing. Part of the reason for this growth has been some recent imports of data from other systems but these were not so significant in and of themselves. Total bulk loads were maybe 10,000 rows. There were 3 or 4 bulk deletes and reimports of these records but still not enough to cause such bloat of our ldf file...I think.

I found a job that reindexes 35 indexes every night regardless of their fragmentation. It issues a dbcc dbreindex command and uses 90 for every fillfactor. I'm thinking this may also cause disk fragmentation which may be contributing to the disk latency issues we have been having on reads and writes.
Clearly that will need to be rethought. Assuming that is straightened out and we're using intelligent reindexing, what factors would I look at to set an optimal file growth. Here are some details that of my installation:
mdf = 194,112Kb
ldf = 19,132,224Kb

We are running an OLTP db with frequent updates and inserts. We are in Full backup mode with transaction log backups every 15 minutes. Its an employee intranet where we handle employee times heets, leave request, purchase orders...stuff that folks are partial to ergo the frequent backups and the FULL mode. We would very much require the transaction logs in the event that a backup were required.

Our backup sizes are currently 120Mbs and they grow an average of 1MB every 4 days or 5Mbs a month

Other options that are set: Auto Update Statistics, Torn Page Detection and Auto Create Statistics.

That is the information I have to go by. If there are other data points I could collect please let me know :) Is there a way to find out what the growth rate of the Transaction logs has been over time? That might be where to look first huh?
I know frequent growth of the ldf can cause performance issues if it has to regrow frequently so I don't want to simply shrink the log to its smallest possible size. But I do want to reclaim space that won't be needed for awhile. Just could use some assistance as to what variables to consider.

We will be moving to SQL Server 2008 in a few months but I'd still like to get this part figured out before the upgrade.

Thank you very much!

Software/Hardware used:
Sql Server 2000 SP4

Answer Wiki

Thanks. We'll let you know when a new response is added.

I would assume that at some point either there weren’t any log backups being taken, or someone did a lot of data moving one day.

Either way, if you aren’t running low of space, don’t bother shrinking the log file. You won’t gain anything by having a smaller transaction log file.

Change the growth from 10% to 512 Megs or 1 Gig and you should be fine.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following