locked
increase data file growth RRS feed

  • Question

  • Hi,

    database current size is 24 GB and  is growing approx. 30 MB per day.
     Database data file is set to unrestricted growth by 1 MB and log file By 10 percent, restricted growth to 2097152 MB. SQL is using 1.56 GB out of 4 GB
    and we have 146 GB free space on drive where database data and log files resides.

    we are getting alerts like...Databases - Dynamic Data File Growths Remaining: Data file growths remaining could not be evaluated because OS data is not available.

    Just wondering how much to set data file growth option? Please advise.
    Friday, February 15, 2013 11:21 PM

Answers

  • Hello,

    You have quite a bit going on here...

    You're main question is what you should set the database autogrowth option to. My advice would be set ti to a number that makes sense but isn't too big. Really, autogrow should only be used as a catch all net and not used for file growth overall. If you know you're growing data at 30MB a day, then pre-size your database for - IMHO - at least 6 months. This means your data files should have at least something around (30 * 7 * 4 * 6) 5 GB of free space for growth and your autogrow set to something around a week (30 * 7) 210 MB. Just be careful, I doubt perform volume maintenance tasks is set so that means (you won't be using instant initialization) your queries will have to wait until all data growth is complete for them to continue processing.

    For your log, instant file initialization can't be used at all, the same rules apply - everythign waits until the growth is done. I would change it from a percentage based growth to a fixed size growth. This I can't help you with though, set it low enough that the growth won't stall too many queries but large enough that it won't be growing every few minutes. Ideally the log should be pre-sized as well so no growth need to occur but if one does you won't cascade growths, reuining performance.

    I would add the MSSQLUsers group to the local security policy item of "perform volume maintenance tasks" so that you can (upon the next service restart) use instant file initialization.

    -Sean


    Sean Gallardy | Blog | Twitter

    Saturday, February 16, 2013 1:53 AM
    Answerer