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.

SQL SERVER - 2005 - SSMS - Enable Autogrowth Database Property autogrowon

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – 2005 – List Tables in Database Without Primary Key
Next Post
SQL SERVER – 2005 – List All The Column With Specific Data Types

Related Posts

11 Comments. Leave new

  • That’s a usefull info, thanks Dave.

    Reply
  • 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?

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

    select * from sysfiles

    Reply
  • 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.

    Reply
  • hi dave
    how can i set the growth file size ……..?
    is it will create any problem during DB transaction??

    Reply
  • 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?

    Reply
  • 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…

    Reply
  • Thanks so much

    Reply

Leave a Reply

Menu