Weird behavior of transaction log growth in SQL Server 2005, 2008, and R2

  • Article

Guys, just so that you are aware. There is a bug in the in the code of the function that grows the file so that whenever the difference between the new size and the current size is a multiple number of pages of any value between 0x80000 and 0x8001F the log will only grow 31 pages (MIN_LOG_FILE_SIZE / PAGESIZE).

For that reason, the following script behaves as nobody would expect:

CREAT DATABASE Test_LogFileGrowth;
GO

ALTER DATABASE Test_LogFileGrowth
MODIFY FILE
(
NAME = Test_LogFileGrowth_Log, SIZE = 8GB
) ;
GO

-- That works.

--Run dbcc sqlperf(logspace)
dbcc sqlperf(logspace);

GO

--Record size of Test_LogFileGrowth log: 8191.992

ALTER DATABASE Test_LogFileGrowth
MODIFY FILE
(
NAME = Test_LogFileGrowth_Log, SIZE = 16GB
);
GO

--Record duration of statement run above: 0s

--Run dbcc sqlperf(logspace)
dbcc sqlperf(logspace)

GO

--Record size of Test_LogFileGrowth log: 8192.992

-- Huh?

This has been fixed in next major release’s code base already. It doesn’t pose any security or data corruption risk to your database or transaction log, only that you may notice this weird way of behaving under those specific circumstances and you may be wondering why it does it that way.

Thanks,

Nacho