Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

New KB Article on SQL Server 2005 File Growth Bug

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: | |
 

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.