3

I have all my databases in full recovery and have the log backups happening every 15 minutes so my log files are usually pretty small. question is if there is a nightly operation that causes lots of transactions to happen and causes my log files to grow, should i shrink them back down afterward?

Does having a gigantic log file negatively affect the database performance? Disk space isn't an issue at this time.

edit: Using SQL 2005 will be using 2k8 soon. (I thought i put it in... d'oh)

3 Answers 3

3

There shouldn't be a performance hit having a large log file. You actually would be taking a performance hit needing to regrow the log file each night when the long running operation happens.
If disk space is not an issue and logs are not completely out of control I would leave them be, but as always keep an eye on them.

2
  • @boezo - I'd say there's some evidence to the contrary: sqlblog.com/blogs/linchi_shea/archive/2009/02/09/… But I'd still avoid auto-growing log files. It's a trade off between maximum performance now, or better performance in the future.
    – Chris
    Apr 15, 2010 at 13:55
  • That is a good article that points to management aspect of the log files and not letting them autogrow. The comment in the post is that if you know you need 10gb just grow it all at once to 10gb. It does not say that having a large log file is bad it says that having a large log file that grew in little chunks at a time is bad.
    – boezo
    Apr 15, 2010 at 20:32
0

Are you running any maintenance overnight, such as a reindex?

Here's a link that may help in deciding whether to shrink:

Also, may I suggest that you update the question with the version of sql that you are using.

3
  • I read that blog entry. They didn't really address the issue of whether or not a big log file is necessarily bad. I know that shrinking is a very expensive operation and this would be done during "downtime" or at least slow time..
    – Rob
    Apr 15, 2010 at 12:57
  • @Rob - I believe there is a slight performance hit for having a large log file (or rather a lot of VLFs), but if you need the extra log space in the future, it is better to pre-grow it rather than let it grow automatically. Ref: blogs.msdn.com/grahamk/archive/2008/05/16/…
    – Chris
    Apr 15, 2010 at 13:45
  • It is very different to say large log file VS a lot of VLFs. A well managed log file can grow quite large and perform quite well while a poorly managed large log file can potentially kill performance.
    – boezo
    Apr 15, 2010 at 20:36
0

If there's an operation every night that's growing it then definitely don't shrink it. They obviously need to be that size and will just grow again.

You must log in to answer this question.

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