I was teaching an internals/maintenance/performance class for Microsoft DBAs this week on the Redmond campus and one of the students (thanks Crystal!) showed me a really strange bug that I’d never seen before.
One of the things that Kimberly and I both recommend is not having transaction log VLFs be too large, with 500MB being our recommended maximum so as not to interrupt the clearing/truncating of the log. See Kimberly’s blog post Transaction Log VLFs – too many or too few? for more details.
If you try to grow a log file by a multiple of 4GB, it will fail on the first attempt and only grow by about 31KB, with no error message. The second attempt will succeed!
We’ve examined the SQL code and it’s a bug that the code miscalculates the growth size when the specified size is an exact multiple of 4GB. Simple type conversion error. It’s fixed in 2008 R2 onward.
This could cause you problems if you follow our advice and try to grow a log file in increments of 8GB to keep the VLF size at 500MB (a growth of 1GB or more create 16VLFs, with each VLF being 1/16th the size of the growth).
I tested this on 2005 SP3 and 2008 SP1.
Here’s an example script to show what I mean. First off I’ll create a database and grow the log file to 1GB:
CREATE DATABASE TestLogFileGrowth;
GO
ALTER DATABASE TestLogFileGrowth MODIFY FILE
(
NAME = TestLogFileGrowth_Log, SIZE = 1GB
);
GO
That took 30 seconds to run, performing the zero initialization of the log file (which must *always* occur – see Search Engine Q&A #24: Why can’t the transaction log use instant initialization?).
Checking the log size:
DBCC SQLPERF (LOGSPACE); GO
Database Name Log Size (MB) Log Space Used (%) Status ------------------- ------------- ------------------ ------- <snip> TestLogFileGrowth 1023.992 0.0733858 0
Now I’ll try to grow the log file by 4GB:
ALTER DATABASE TestLogFileGrowth MODIFY FILE
(
NAME = TestLogFileGrowth_Log, SIZE = 5GB
);
GO
This took zero seconds. Impossible if it actually grew the log by 4GB and zero initialized it!!
Let’s check the log size:
DBCC SQLPERF (LOGSPACE); GO
Database Name Log Size (MB) Log Space Used (%) Status ------------------ ------------- ------------------ ----------- <snip> TestLogFileGrowth 1024.234 0.07994729 0
Weird! It only grew by 1MB! If I try the grow again (and as long as I don’t specify a size that’s an exact multiple of 4GB from the current size), it takes a couple of minutes and grows correctly.
Don’t be surprised if you see this. (Note: the bug is fixed from SQL Server 2008 R2 onward.)
Cheers
13 thoughts on “Bug: log file growth broken for multiples of 4GB”
Seen it and reported it a while ago :)
http://connect.microsoft.com/SQLServer/feedback/details/481594/log-growth-not-working-properly-with-specific-growth-sizes-vlfs-also-not-created-appropriately
If it grew by 1MB on the first attempt, then the 2nd attempt was slightly less than a growth of 4 GB. Is this why it suceeded the second time? Because the growth attempt wasn’t exactly 4 GB?
Yup – I discussed the code with Ignacio in the current MCM rotation and there’s a clear bug in there.
I added the following comment to the bug on Connect:
The reason it suceeds on the 2nd try is because the log file grew a little bit the 1st try, you are no longer trying to grow it by the same increment. If you adjust the growth slightly on repeat attempts to make sure that you are trying to grow it by a multiple of 4 GB, you get the same behavior. In other words, you can make it fail every attempt by choosing a growth factor in the "sweet spot".
Is this possibly related at all to the bug Kimberly mentioned (link in the second paragraph) about growth by exactly 4096MB not getting divided into equally sized VLFs? It may not be, but it seems an odd coincidence that both of them happen for the same growth increment. Then again, correlation doesn’t always prove causation.
Nope – it’s a simple type conversion error in the log manager code.
So, if the "autogrow" is set to 4000MB, that should be fine?
It only fails when it is set to a multiple of 4096?
Yes, that would work but I would really hesitate to set the log auto-growth that high. That would mean all write activity in the database stops while the 4000MB is zero initialized.
Are there any issues with performance/efficiency if the filesize for each of the VLFs in the log file are a different size? I ask this because I was experimenting with the different initial sizes then manually growing the log files and noticed a difference. One example is I created a db with an initial log file size of 8000MB then I manually grew the log file to 16000MB then to 24000MB. What I noticed was the size of the 24 new VLFs were exactly 500MB in size but the initial 16 VLFs came out to be slightly under 500MB at 499.9375 when I used the dbcc loginfo.
Nope – nothing to worry about. The algorithm isn’t guaranteed to produce exactly the same sized VLFs.
Apologies about the late response, but was this bug fixed in later service packs/CU’s for SQL 2008 (non R2), or only from SQL 2012 and up?
Thanks!
I believe it’s only for 2008 R2 and above
Correct. https://support.microsoft.com/en-us/kb/2633151