Best practice for Data size/Log Size ratio

[Jeffrey Sheldon via SQLMonster.com]

Just wanted to know what is a general rule of thumb when determining log file space against a database's data file.

We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month process of remove) that can balloon the log file size.

Should it be 10% of the Data file, i.e. if the Date file size is 800MB the log file should be 8MB?

I realize there are a myraid of factors that go against file size but a general starting point would be nice.

Thanks
Jeff

--
Message posted via http://www.sqlmonster.com

[Simon Hayes]

Re: Best practice for Data size/Log Size ratio


"Jeffrey Sheldon via SQLMonster.com" <forum@SQLMonst er.com> wrote in message
news:5a05760083 9f49958dcc19b7f 175f702@SQLMons ter.com...[color=blue]
> Just wanted to know what is a general rule of thumb when determining log
> file space against a database's data file.
>
> We allow our data file for our database to grow 10%, unlimited. We do not
> allow our log file to autogrow due to a specific and poorly written
> process (which we are in a three month process of remove) that can balloon
> the log file size.
>
> Should it be 10% of the Data file, i.e. if the Date file size is 800MB the
> log file should be 8MB?
>
> I realize there are a myraid of factors that go against file size but a
> general starting point would be nice.
>
> Thanks
> Jeff
>
> --
> Message posted via http://www.sqlmonster.com[/color]

I don't believe there's any MS recommendation on this, although I'm happy to
be corrected. I think I read a post from an MVP saying he uses the size of
the largest table plus 10%; personally, with no other information to base a
decision on, I would go for 20% of the size of the data file(s). But as you
say, there are a myriad of factors, so you may as well just pick a number
(do you have another similar database to compare against, perhaps?), and
then adjust it as you go along. Beware of auto-grow/shrink, though, which
can be a bit of a pain if it kicks in at the wrong time - it's probably
better to go with a fixed size and too large rather than too small,
especially since disk space is relatively cheap.

Simon

[Jeffrey Sheldon via SQLMonster.com]

Re: Best practice for Data size/Log Size ratio

Thanks Simon,

I will use our largest database. It is funny that this is really a trail and error process (I remember some formula of calculating the sizes but even that is not an exact science)

Jeff

--
Message posted via http://www.sqlmonster.com

[Pablo Sanchez]

Re: Best practice for Data size/Log Size ratio

"Jeffrey Sheldon via SQLMonster.com" <forum@SQLMonst er.com> wrote in
news:83b1d8a99b 754bd39177bda2d 37d6b64@SQLMons ter.com:
[color=blue]
> Thanks Simon,
>
> I will use our largest database. It is funny that this is really
> a trail and error process (I remember some formula of calculating
> the sizes but even that is not an exact science)[/color]

The log is akin to a journal: it captures all the changes to your
database. To size it, make it large enough to hold all the expected
changes to the database between log dumps. I'd suggest you add a
buffer to the size for the 'unexpected' changes.

Also, if you have a large log, it's not going to impact performance.
You may waste some space but disk space is relatively cheap.

On a different note, when you make a change to a table, the table
isn't really changed, the changes go to the log. Only at
'checkpoint' are the changes in memory synchronized with the table.
But you probably already knew that ...
--
Pablo Sanchez - Blueoak Database Engineering, Inc

http://www.blueoakdb.com

[getinked]

Re: Best practice for Data size/Log Size ratio

I agree w/ Pablo in having a large log wont impact performance if you
have space on your server to accomadate this. Your going to take hits
on performance everytime your log has to grow. so if your bd isnt a
high transaction db and there are few changes throughout the day a
smaller log would be fine eg. 10% or something of that nature. DB's w/
high volumes of transactions throughout the day will fill up the log
faster making it autogrow a lot thus hurting performance a little. I
feel everyone's DB's a different in that aspect so you just need to
monitor it for a bit and see what works best for you.