http://www.sqlservercentral.com/blogs/brian_kelley/2006/07/13/new-kb-article-on-sql-server-2005-file-growth-bug/

Printed 2014/09/02 04:02AM

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

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.