New KB Article on SQL Server 2005 File Growth Bug

2006/07/13

There is are two new KB articles on how the file growth value can become abnormally large. You can find the details here.

FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005
FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1

A simple way to demonstrate the issue. Run the following script (this assumes you have a directory at C:\Data... if not, modify accordingly) to create the database and verify the growth column:

CREATE DATABASE [TestGrowth] ON PRIMARY
( NAME = N'TestGrowth',
FILENAME = N'C:\Data\TestGrowth.mdf' ,
SIZE = 2048KB ,
FILEGROWTH = 10% )
LOG ON
( NAME = N'TestGrowth_log',
FILENAME = N'C:\Data\TestGrowth_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 10%)
GO

EXEC sp_helpdb TestGrowth
GO

Once that is done, change the file growth on the TestGrowth file:

ALTER DATABASE TestGrowth
MODIFY FILE (NAME = TestGrowth, FILEGROWTH = 2MB)
GO

EXEC sp_helpdb TestGrowth
GO

Note that the file growth is now in terms of KB. Now, take the database offline, bring it back online, and then check the file growth again:

ALTER DATABASE TestGrowth
SET OFFLINE
GO

ALTER DATABASE TestGrowth
SET ONLINE
GO

EXEC sp_helpdb TestGrowth
GO

When I ran it, filegrowth no longer showed at 2048 KB, but rather 256%. There are hotfixes available for both SQL Server 2005 RTM and SP1 (they are different files).

Technorati Tags: SQL Server | Microsoft SQL Server | SQL Server 2005