3

OK we all know that a big log file crumbles DB performance.

Today we were analyzing a client's server and saw some log files about 3900% bigger than the data file.

And that made me curious if is there any best ratio between the two of them?

2
  • How does a big log crumble perfromance? Feb 9, 2010 at 14:07
  • bit of a misnomer that a big log= bad performance. Guess it comes from default conditions where for a log file to get that big, its probably been autogrown many times Feb 9, 2010 at 15:48

2 Answers 2

3

I don't think there are any hard rules about the ratio of data files to log files for sql server.

In simple terms, log files are there as a record of change in the database & can be used for recovery purposes. The faster the rate of change of your data, the bigger your log file will probably need to be.

What you're seeing with your clients is more likely an issue where the databases are in full recovery mode, full backups are being performed without log backups. Your client needs to setup a proper log backup strategy for these databases.

If point-in-time recovery isn't needed (i.e. daily/weekly full backups is good enough) then changing to simple recovery mode & a one-off log shink should be enough.

1
  • 14 seconds faster, and a more complete answer than mine. Bravo.
    – mfinni
    Feb 9, 2010 at 2:24
0

Assuming MS SQL server, although I think this applies to any RDBMS : a big log doesn't tend to affect performance per se. Bad disk planning for the DB and log files that leads to IO problems is what will cause bad performance.

A big log file is due to an enormous number of writes since the last log file backup. Generally, this means that you're not backing up and truncating the log file properly and/or often enough.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .