I was on the MSDN forums recently discussing SharePoint database index fragmentation and spotted a question around SharePoint database autogrowth that I thought was interesting. Although this has been raised several time in the past (Alex Dean blogged about it back in 2009) , I thought it worth clarifying this once more now that SharePoint 2010 is RTM.
- As much as possible, pre-grow all data and log files to their anticipated final size.
- We recommend that you enable autogrowth for safety reasons. Do not rely on the default autogrowth settings. Consider the following guidelines when configuring autogrowth:
- When you plan content databases that exceed the recommended size (200 GB), set the database autogrowth 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 operation that involves filling the new space with empty pages.
- Set the autogrowth value for the Search service application Property Store database to 10 percent.
- 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 within a year — with 20 percent additional margin for error — by using the ALTER DATABASE MAXSIZEproperty. Periodically review this setting to make sure it is still an appropriate value based 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.