What should an "accidental" DBA know about VLFs?

For example,

Should VLFs be checked often or is this only a "set it and forget it" issue if you get the configurations set well?

How many are too many? It depends on ?

Has there been a correction for the 4GB bug? ([Mentioned here][1])

[1]: http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx
more ▼

asked Jan 12, 2011 at 11:08 AM in Default

Mark gravatar image

2.6k 23 25 27

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest
Paul Randal has a great article you can read here Transaction Logs it addresses this issue. He also has a few blog articles on his site addressing this as well. Another great blog about this can be found here. SQLSKILLS - 8 steps to better transaction log throughput
more ▼

answered Jan 12, 2011 at 11:14 AM

Tim gravatar image

36.4k 38 41 139

Thanks for all the answers, I didn't know much about VLFs until Steve Jones asked a question about them for the SCC question of the day.
Jan 13, 2011 at 10:42 AM Mark
(comments are locked)
10|1200 characters needed characters left

The resources I use regarding VLF's are the one @TRAD mentioned above. I check once in a while all the VLF's in my critical databases. The action you take depends on your environment but the recommendation I often here is if no of VLF's is around 50 is considered fine but if it is >100 it is TOO MANY and you need to take action and I suggest you check it once in a while too.

I use this script to check the VLF's in all the databases


Just to add this blog I found from Glenn Berry on [Detecting and Reducing VLFs in SQL Server 2008/2008 R2 Transaction Log Files][2]

[2]: http://sqlserverperformance.wordpress.com/2010/06/22/detecting-and-reducing-vlfs-in-sql-server-20082008-r2-transaction-log-files/
more ▼

answered Jan 12, 2011 at 11:47 AM

DaniSQL gravatar image

4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left

As the others have said, VLFs need to be monitored and you should aim to keep their count low.

The number of VLFs is not something that is set and forget, they are created when a transaction log file is created or (auto)grown. Following Kim Tripp's advice mentioned in @TRAD's answer will ensure you have pre-sized your tran log as needed and have a predictable number of VLFs. The growth of a tran log is split into VLFs and the split size depends upon the growth amount.

The breakdown of growth and VLF count is available here: [Transaction Log VLFs too many or too few][1]

  • chunks less than 64MB = 4 VLFs
  • chunks of 64MB and less than 1GB = 8 VLFs
  • chunks of 1GB and larger = 16 VLFs

The bug has been fixed for SQL11 (as mentioned in the big report from Paul that you linked to) and can be temporarily avoided by growing by a multiple that is slightly off from 4GB (4000MB is a good example).

[1]: http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
more ▼

answered Jan 12, 2011 at 01:47 PM

WilliamD gravatar image

25.9k 17 19 41

Thanks William, that's some good information. As for the 4GB bug, I was hoping MS would resolve that for the older versions too rather than making it an upgrade requirement. But apparently not, although it is easy to work around it.
Jan 12, 2011 at 03:27 PM Mark

@Mark - maybe they will fix it with an SP, I am not sure though. It depends on how deep MS goes with their SPs and if they would fix that portion of the engine.

Paul Randal seems to allude to an error in a type conversion (it normally boils down to something simple!). If that were the case, an SP would be a viable way of pushing that out to vCurrent and vCurrent-1 too (2K8R2 & 2K8 respectively).
Jan 12, 2011 at 11:41 PM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 12, 2011 at 11:08 AM

Seen: 2545 times

Last Updated: Jan 12, 2011 at 11:33 AM