Autogrowth of SharePoint Content Databases – an Automated Solution
March 5, 2012 1 Comment
During a recent presentation at the London SharePoint User Group by Steve Smith of Combined Knowledge about SharePoint administration (and many other things), he discussed the issues surrounding the auto-growth of SharePoint content databases and the possible performance ramifications these can have when they are triggered during business hours.
As Steve pointed out, the default auto-growth settings for a newly created content databases are to grow in 1MB increments:
Clearly for a content database that is used off the bat with this configuration, a lot (and I mean a lot) of auto-growths will be performed on the database as users load content and even access the site collections that the content database contains. The recommendations from Microsoft are to pre-grow data and log files and to set the auto-growth to 10% – see Storage and SQL Server capacity planning and configuration (SharePoint Server 2010) for further details: http://technet.microsoft.com/en-us/library/cc298801.aspx.
These recommendation rightly point out that database growth should be proactively managed. So Steve’s presentation and this article got me thinking about a repairable SharePoint health analyzer rule that could warn when content databases are filling up and, if required, grow them automatically. What makes this a practical solution I believe is the ability to configure the rule so that database growths performed by the repair action of the health rule are only executed within a specified time window.
The health rule derives from SPRepairableHealthAnalysisRule so it can be configured to automatically repair (for repair read grow) a database once it has exceeded a configurable capacity threshold. The rule supports four custom configurable parameters:
<Properties> <!-- Enter the database capacity percentage that is used to trigger --> <!-- a warning and potentially a scheduled database expansion. Values --> <!-- should be between 0.0 and 1.0. --> <Property Key="CapacityThreshold" Value="0.8" /> <!-- Enter the BeginHour for the time period in which database --> <!-- expansions should occur. --> <Property Key="BeginHour" Value="1" /> <!-- Enter the EndHour for the time period in which database --> <!-- expansions should occur. --> <Property Key="EndHour" Value="3" /> <!-- Enter the percentage of growth the database should undertake --> <!-- during an expansion. Values should be between 0.0 and 1.0. --> <Property Key="GrowBy" Value="0.3" /> </Properties>
The CapcityThreshold property is used to set the level at which warnings about database capacities are raised. Once a database exceeds 80% (the default threshold for the rule) a health analyzer warning is raised and is visible in central admin.
The BeginHour and EndHour properties are used to define a time window in which, for database that have exceeded their capacity threshold, growths should be executed by the rule. These growths will not occur if the ‘Repair Automatically’ button is pressed outside of this window. Ideally you should review the properties and behaviour of this rule and if appropriate, set the rule to repair automatically. Please note, in order for the rule to repair automatically during the specified time window, the rule schedule should remain hourly:
Lastly, the GrowBy property is used by the repair method to determine the amount of expansion a database should undertake. The default option is 30% – this means that if a database is 100MB in size and 90% full, the database will be grown to 130MB. The total database size is used to calculate the new database size and not the amount of space currently used.
The rule is packaged as part of the SharePoint Health Analyzer Rules project on http://sphealth.codeplex.com/
The source code for the rule can be reviewed here: http://sphealth.codeplex.com/SourceControl/changeset/view/412d4aba56ba#SPHealth.SharePoint.HealthRules%2fSP%2fRules%2fPerformance%2fWarnDatabaseCapacity.cs
BTW: There is a quicker way to solve this entire auto-growth problem – make the content database read-only!