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 the SQL11 code already.

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.

Cheers