Bug in Log Growths in 4GB increments (fixed for SQL2008R2)

SQL Server like other software unfortunately has bugs, and thankfully these are often resolved in the subsequent Service Packs & Hotfixes released

One such bug was the incorrect growth of the log file, if the growth increment was any multiple of 4GB - it doesn't grow with the expected growth size

 

For those who didn't know

As log growths are split in a number of equally sized number of VLFs, with that number of VLFs dependent on the overall growth increment size

For a growth increment that is less than 64MB, this will grow the file in 4 VLFs (equally sized), and with a growth increment size between 64MB to 1GB this is in 8 VLFs, finally for the growths > 1GB this is in 16 VLFs


Paul Randal blogged on this bug way back in May 2010 - that's when I learned about the 4GB increment issue

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

I recommend that you also read this entry VLFs by Kimberly http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx#ixzz1lXeN9jNy

Subsequently my growths have been primarily in 2GB Growths, thus 16 VLFs each being 128MB (Once this hotfix is in place I can perhaps use the 4GB interval I want larger VLFs)

Finally a Connect item can help highlight such issues and ultimately even get us a fix - I try to review these regularly and vote for those you agree should be resolved

http://connect.microsoft.com/SQLServer/feedback/details/481594/log-growth-not-working-properly-with-specific-growth-sizes-vlfs-also-not-created-appropriately


Well for SQL Server 2008R2 this is being now addressed with the SP1+CU4 or CU11, details are documented in this KB http://support.microsoft.com/kb/2633151

It doesn't seem that there is also a fix for SQL Server 2008 - if this does become known I blog those details also

Published Sunday, February 5, 2012 8:21 PM by NeilHambly
Filed under:

Comments

No Comments