none
SP2013 DB Growth Factor Settings

    Question

  • I am looking for information/recommendations for SQL Auto-growth Factor settings for SP2013 db’s. Specifically, I am looking for auto-growth rate settings for different types of databases.

    SP Best Practices recommend increasing CONTENT db’s by a fixed number of mb’s as opposed to percentage (see below). I assume the same recommendation holds for all SP db’s, although I am speculating on this….

    My question is: What number of mb’s (increment) should we set the auto-growth feature for each type of db?

    Content – XXX mb
    Config – XXX mb                                  
    Usage/logs – XXX mb
    Search – XXX mb
    UPS (all)
     
    ANY feedback is much appreciated!

    ------------------------------------------

    From Technet: http://technet.microsoft.com/en-us/library/cc298801.aspx#Section6_3

    Proactively manage the growth of data and log files

    We recommend that you proactively manage the growth of data and log files by considering the following recommendations:

    • As much as possible, pre-grow all data and log files to their expected final size.<o:p></o:p>
    • We recommend that you enable auto-growth for safety reasons. Do not rely on the default auto-growth settings. Consider the following guidelines when you configure auto-growth:
      • When you plan content databases that exceed the recommended size (200 GB), set the database auto-growth value to a fixed number of megabytes instead of to a percentage. This will reduce the frequency with which SQL Server increases the size of a file. Increasing file size is a blocking action that involves filling the new space with empty pages.<o:p></o:p>
      • If the calculated size of the content database is not expected to reach the recommended maximum size of 200 GB within the next year, set it to the maximum size the database is predicted to reach in a year — with 20 percent additional margin for error — by using the ALTER DATABASE MAXSIZE property. Periodically review this setting to make sure that it is still an appropriate value, depending on past growth rates.
    • Maintain a level of at least 25 percent available space across disks to allow for growth and peak usage patterns. If you are managing growth by adding disks to a RAID array or allocating more storage, monitor disk size closely to avoid running out of space.<o:p></o:p>

    - Rick

    Tuesday, October 01, 2013 3:40 PM

Answers

  • Generally, if you can plan for it, you should not use autogrowth, instead setting the initial size appropriately and manually growing the database as-needed.  This isn't always workable, so you want to minimize the autogrowth events.  For example, try starting out with 100MB for your autogrowth settings.  Monitor the autogrowth events, make sure they're not happening too frequently, or at least not impacting performance (perhaps your underlying disk subsystem is fast enough that autogrowth isn't something to be concerned about).

    Setting the autogrowth setting too low will cause frequent database growth, which is not good for performance, but setting it too high will cause performance issues during the autogrowth (because it takes too long to complete).

    Never use a percentage for autogrowth, it will exponentially impact performance and possibly disk space.

    But again, for what it's worth, I use 100MB for MDF/LDF.


    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    • Marked as answer by SP RickyRick Tuesday, October 01, 2013 5:10 PM
    Tuesday, October 01, 2013 5:05 PM
    Moderator

All replies

  • Generally, if you can plan for it, you should not use autogrowth, instead setting the initial size appropriately and manually growing the database as-needed.  This isn't always workable, so you want to minimize the autogrowth events.  For example, try starting out with 100MB for your autogrowth settings.  Monitor the autogrowth events, make sure they're not happening too frequently, or at least not impacting performance (perhaps your underlying disk subsystem is fast enough that autogrowth isn't something to be concerned about).

    Setting the autogrowth setting too low will cause frequent database growth, which is not good for performance, but setting it too high will cause performance issues during the autogrowth (because it takes too long to complete).

    Never use a percentage for autogrowth, it will exponentially impact performance and possibly disk space.

    But again, for what it's worth, I use 100MB for MDF/LDF.


    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    • Marked as answer by SP RickyRick Tuesday, October 01, 2013 5:10 PM
    Tuesday, October 01, 2013 5:05 PM
    Moderator
  • Thanks as always Trevor...much appreciated!

    - Rick

    Tuesday, October 01, 2013 5:10 PM