SQL Server 2005 Database Autogrowth option is a bug

ash

– December 7, 2010Posted in: Database, How-to Guide

If you've enjoyed reading this post then please subscribe to my Full Text RSS Feed.

SQL Server 2005

When maintaining a SQL Server database, you have the option to select if you want to restrict the growth of a database or transaction log or give SQL Server the liberty to grow automatically without setting a restriction. You select unrestricted file growth when you know you have enough disk space to cope with the growth of the database or transaction log over a period of time.

Recently, we had to make adjustments to cater for a larger transaction log but we were stumped when the “Unrestricted File Growth” option radio button kept reverting back to the “Restricted File Growth (MB)” option and the size of 2,097,152 MB. Every time I select the “Unrestricted File Growth” radio button and hit the save button, it reverts back to “Restricted File Growth (MB)”.

I discovered from Microsoft that it is a bug but Microsoft refused to call it a “bug”. Their explanation – there is a set limit of 2TB for “Unrestricted File Growth” for any database or transaction log. The Restricted File Growth (MB) size of 2,097,152 MB is 2TB. This means that the Restricted File Growth (MB) option is actually allowing auto growth until it reaches 2TB. This also means they have also confused database administrators by providing the “Unrestricted File Growth” option which is not selectable in SQL Server 2005.

So if you wish to allow your SQL Server database or transaction log to grow without a limit, select “Restricted File Growth (MB)” option and allow the database or transaction log to grow until it reaches the 2TB limit. That is the only work around for now.

See screenshot below.