Post reply

Restrict Autogrowth size( File Growth ) of a Database to 10 percent :)

  • June 20, 2011 at 5:03 am

    #150302

    I wish to restrict Autogrowth size ( File Growth ) of all the data files and log files of the Users Database to 10 percent.

    I know how to do it through GUI ( Manually ) , but i am looking for an SQL script with which i can do it at once ( for all the mdf and ldf files of a database - as they are more in numbers )

    Please provide your inputs / scripts ....

    Please cc to my id also : abhishek.dwivedi798@gmail.com.

    Thanks

  • June 20, 2011 at 5:54 am

    #1341098

    Try this

    ALTER DATABASE database MODIFY FILE ( NAME = N'database_log', FILEGROWTH = 10% )

    you could use sp_msforeachdb , I wouldnt though.

    Jayanth Kurup[/url]

  • June 21, 2011 at 1:30 am

    #1341658

    SP_MSForEachDb 'SELECT table_name from [?].information_schema.tables

    where table_name = "table1"'

    FYI

  • June 21, 2011 at 12:39 pm

    #1342049

    10% is a dangerous file growth. Recommendation is not to use % (as it can get out of control as the file grows) but to set it to a sensible fixed amount.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 22, 2011 at 1:47 am

    #1342254

    Thanks for the info .

    As, I am not very much aware based on which i should set my filegrowth size , I am opting for 10 % autogrowth .

  • June 22, 2011 at 1:17 pm

    #1342674

    What Gail is trying to say is that by auto growing 10% each time, the size will grow. As an example - if you have a 100 GB database with auto grow set to 10%

    First time 100 + 10% = 110GB

    Second time 110 + 10% = 121GB

    Third time 121 + 10% = 133.1GB

    As you can see you are grabbing a larger chunk of drive space each time instead of something much more manageable like a flat 10GB

    Chris Powell

    George: You're kidding.
    Elroy: Nope.
    George: Then lie to me and say you're kidding.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply