|
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)
(comments are locked)
|
|
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 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 '11 at 10:42 AM
Mark
(comments are locked)
|
|
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 EDIT: Just to add this blog I found from Glenn Berry on Detecting and Reducing VLFs in SQL Server 2008/2008 R2 Transaction Log Files
(comments are locked)
|
|
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
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). 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 '11 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 '11 at 11:41 PM
WilliamD
(comments are locked)
|
