Read more about the issue at http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
At my current client I have seen some databases (like ReportServer database) with more than 2000 VLF files, due to the fact that initial size have been too small and autogrow setting of the log file have been 10%.
If I create a database I always tries to size the data and log files as correct as possible, but I haven't been aware about the 8Gb recommendation. Does it affect the performance or is it solved?
I have read this question [http://ask.sqlservercentral.com/questions/31282/what-should-an-accidental-dba-know-about-vlfs] but there are no answers about the performance impact.: http://ask.sqlservercentral.com/questions/31282/what-should-an-accidental-dba-know-about-vlfs
The recommendation is not necessarily 8GB (it always depends), but that the VLFs are created depending upon the size the log file should be (or should grow by).
8GB just means that the VLFs are 512MB (8GB / 16VLFs), which means that 512MB will be cleared from a VLF when it has been processed - this is a lot nicer than if you pre-allocate 64Gb which results in 16*4GB VLFs.
E.G. If you need a 64GB log file, then start by making it 8Gb, then extend to 16 etc. until you hit 64GB, this will ensure that you have 128 VLFs each 512MB in size.
I think it may be something that you wouldn't necessarily notice in a low load system, but it is a good thing to manage if you are so inclined. The number of VLFs directly impacts performance of activity in the transaction log and can affect tran log backups too ([Kim Tripp]).
p.s. The 4GB bug mentioned in the question you linked to has been fixed as of SQL 11. I would hope they retro-fit SQL 2008 via an SP, but maybe they won't.: http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
answered Jan 19, 2011 at 04:28 AM
I havent heard that its resolved so am avoiding it until I do... I may have been day-dreaming when the announcement went out though
answered Jan 19, 2011 at 02:49 AM