Autogrowth of SharePoint Content Databases – an Automated Solution

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!

Reduce Page Weight

Whilst working with SharePoint Master Pages is can be quite common to have to remove ContentPlaceHolders that contain content or controls that aren’t relevant to the design of the site.

If you simply remove the unwanted  <asp:ContentPlaceHolder elements from the master page, you’ll break the site and receive an error when viewing any pages that use your master page.

To work around this is can be quite common to see code that looks something like this used in custom master pages:

<div style="display:none;">
                      <asp:ContentPlaceHolder ID="PlaceHolderPageTitleInTitleArea" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderGlobalNavigation" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderGlobalNavigationSiteMap" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderPageImage" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderTitleLeftBorder" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderMiniConsole" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderTitleRightMargin" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderTitleAreaSeparator" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderNavSpacer" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderLeftNavBarBorder" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderBodyLeftBorder" runat="server"/>
</div>

In the above example, the unwanted ContentPlaceHolders are contained inside a div that is then hidden via the style attribute "display:none;". The result of this is that the page will render and the unwanted ContentPlaceHolders are not shown to the user.

However, this technique still transfers all the HTML that these ContentPlaceHolders contain down to the client and bloat the page weight with content that is never meant to be shown.

The recommended approach resolving the transmission of unnecessary HTML is to replace the div that is used to hide the unwanted content with a hidden asp Panel that runs server-side. The HTML rendered by the ContenPlaceHolders inside this Panel is never transmitted to the client and the page weight is reduced. This can increase load times and reduce load on the server which is never a bad thing!

The example below show the recommended code:

<asp:Panel visible="false" runat="server">
                      <asp:ContentPlaceHolder ID="PlaceHolderPageTitleInTitleArea" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderGlobalNavigation" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderGlobalNavigationSiteMap" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderPageImage" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderTitleLeftBorder" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderMiniConsole" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderTitleRightMargin" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderTitleAreaSeparator" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderNavSpacer" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderLeftNavBarBorder" runat="server"/>
                      <asp:ContentPlaceHolder ID="PlaceHolderBodyLeftBorder" runat="server"/>
</asp:Panel>

I hope this helps….