Excessive VLFs

• Toreador13

  Valued Member

  Points: 74

  More actions

  February 10, 2009 at 9:48 am

  #206371

  I have found contradictory information, but do excessive VLFs actually affect the performance of a sql2005 server? I found one article stating that 2005 seemed to have fixed the problem, but I have found nothing else to support this claim. It seems to me that it would be hard to completely fix, and that as your VLFs climb it would have to affect the server in some ways.

  Thoughts?

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  March 6, 2009 at 3:21 am

  #954725

  Have you read this article by Tony Rogerson:

  http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/07/25/sql-2000-yes-lots-of-vlf-s-are-bad-improve-the-performance-of-your-triggers-and-log-backups-on-2000.aspx

  It seems the problem (based on Tony's experimentation) is not present in SQL 2005.

  Here's another article on the subject, by Kimberly L. Tripp, focusing on how to prevent VLFs:

  http://sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

  And more:

  http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com