Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
How does a big log crumble perfromance? –  SuperCoolMoss Feb 9 '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 9 '10 at 15:48

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
14 seconds faster, and a more complete answer than mine. Bravo. –  mfinni Feb 9 '10 at 2:24

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.