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?
Asked
Active
Viewed 2,031 times
3
-
How does a big log crumble perfromance? – SuperCoolMoss Feb 09 '10 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 – Nick Kavadias Feb 09 '10 at 15:48
2 Answers
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.
Nick Kavadias
- 10,758
- 7
- 36
- 47
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.
mfinni
- 35,711
- 3
- 50
- 86