SQL SERVER – 2005 – SSMS – Enable Autogrowth Database Property

We can use SSMS to Enable Autogrowth property of the Database.

Right-click on Database click on Properties and click on Files. There will be column of Autogrowth, click on small box with three (…) dots.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

10 thoughts on “SQL SERVER – 2005 – SSMS – Enable Autogrowth Database Property

  1. Hi Dave,

    Good article. On one of my DB instances, SQL Server always reset this to “Restricted File Growth”. I tried few times to define Unrestricted File Growth, and server always reverts it bac to restricted. Any idea what could be wrong?

  2. Even though I change the autogrowth with method above, its still 1280% in sysfiles like when I do:

    select * from sysfiles

  3. I have a problem with the auto growth, for some reason sql 2005 changes the auto growth to grow by either 6400 or 1440 %. i cant disable it because there is more inserting then selecting.
    if anyway could could give me a solution it will really be appreciated.

    regarads gavin
    please mail it to me.

  4. I am trying to figure out code to run every time a database is created, so this would all be part of a trigger on create of any database. My idea is to change the autogrowth for that database if it meets certain requirements. Right now when i execute the code it executes fine, but will not actually change the autogrowth for the database :(.

    DELCARE @dbname nvarchar(100), @File1Growth nvarchar(10), @File2Growth nvarchar(10)
    SET @bdname = ‘TempDatabase’

    SET @SQLSTRING =
    (
    ‘ USE ‘ + @dbname +
    ‘ SET ‘ + @File1Growth + ‘ = ‘ +
    ‘ ( ‘ +
    ‘ SELECT sysfiles.growth ‘ +
    ‘ FROM sysfiles ‘ +
    ‘ WHERE sysfiles.FileId = 1 ‘ +
    ‘ ); ‘ +

    ‘ SET ‘ + @File2Growth + ‘ = ‘ +
    ‘ ( ‘ +
    ‘ SELECT sysfiles.growth ‘ +
    ‘ FROM sysfiles ‘ +
    ‘ WHERE sysfiles.FileId = 2 ‘ +
    ‘ ); ‘ +

    ‘ IF ‘ + @File1Growth + ‘ <= 1 ' +
    ' ALTER DATABASE ' + @dbname +
    ' MODIFY FILE (NAME = ' + @dbname + ',FILEGROWTH = 4MB);' +

    ' IF ' + @File2Growth + ' <= 10 ' +
    ' ALTER DATABASE ' + @dbname +
    ' MODIFY FILE (NAME = ' + @dbname + '_Log,FILEGROWTH = 4MB);'
    );

    EXEC SP_EXECUTESQL @SQLSTRING

    I am wondering if anyone has any ideas that may work?

  5. Hi Pinal ,
    When i found this error wile running my SP I went to Enable Autogrowth but it was already set still i m facing this error.
    and one more thing my sp is not giving same behavior for same input some times it gives me error after 40 iterations or some time 200 .It have total no of iteration is 331.

    STRUCTURE OF SP IS Like:

    While()
    BEGIN
    While()
    BEGIN

    While()
    BEGIN

    END
    END
    END

    Means more complex…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s