SharePoint 2010 introduced the Health Analyser feature which is designed to enable
administrators to schedule regular, automatic checks for potential configuration, performance and usage problems in the server farm.
Awesome. But also
in some cases, errors are repaired automatically as soon as they are found, and farm administrators are informed of the repairs.
Now the health analyser is a cool feature, but “repaired automatically” makes me nervous.
I found no currently existing Microsoft documentation on the detail behind the out-of-the-box health rules, whether that be a comment on how serious Microsoft treats this function or my search engine skills I’ll leave comments for another time.
This post is to show what the health rules do, at least those concerning maintenance of the SQL database layer. But first a little background.
There are two types of health rule, those that are capable of automatically repairing certain situations (SPRepairableHealthAnalysisRule) and those that simply flag a warning (SPHealthAnalysisRule). Both rule types implement a method called Check() which is executed by a timer job at the scheduled time.
The SPRepairableHealthAnalysisRule also implements a method called Repair() which is executed when the Check() method returns a fail status code.
There are a number of timer jobs provided out-of-the-box that are used to execute the health rules.
Each health rule defines its scope to match one of these timer jobs, which can then be scheduled according to your maintenance plan.
Databases used by SharePoint have fragmented indices
Assembly | Microsoft.SharePoint.Health |
Namespace | Microsoft.SharePoint.Administration.Health.DatabasesAreFragmented |
What does Check() do? | Always returns fail status |
What does Repair() do? | After checking the farm is available, online, the DB is not current process, etc then it makes a call to [dbo].[proc_DefragmentIndices] |
Timer Job Run Location | Any application server |
Default Schedule | Daily at 12:00am |
Repair Automatically | Yes |
NOTE: the Repair() method does not seem to attempt to reorganise database indices, but rather waits for fragmentation to exceed 30% and then rebuild. This is also done with table locks rather than page or row locks and so is effectively an offline exercise. For more information read Reorganizing and Rebuilding Indexes (for SQL Server 2008 R2)
Databases used by SharePoint have outdated index statistics
Assembly | Microsoft.SharePoint.Health |
Namespace | Microsoft.SharePoint.Administration.Health.StatisticsAreOutOfDate |
What does Check() do? | Always returns fail status |
What does Repair() do? | After checking the farm is available, online, the DB is not currently in a backup/restore process, etc makes a call to [dbo].[proc_UpdateStatistics] |
Timer Job Run Location | Any application server |
Default Schedule | Daily at 12:00AM |
Repair Automatically | Yes |
Databases exist on servers running SharePoint Foundation
Assembly | Microsoft.SharePoint.Health |
Namespace | Microsoft.SharePoint.Administration.Health.DatabasesAreOnAppServers |
What does Check() do? | After checking the farm is not a single server count the number of DBs per service instance on local |
What does Repair() do? | n/a |
Timer Job Run Location | ALL application servers |
Default Schedule | Weekly on Sunday 12:00AM |
Repair Automatically | No |
Search – One or more property databases have fragmented indices
Assembly | Microsoft.Office.Server.Search |
Namespace | Microsoft.Office.Server.Search.Administration.Health.PropertyStoresAreFragmented |
What does Check() do? | For all property DBs not currently in a backup/restore/upgrade process makes a call to [dbo].[proc_MSS_DefragSearchIndexes] with a parameter indicating no repair will be made |
What does Repair() do? | Makes a call to [dbo].[proc_MSS_DefragSearchIndexes] with a parameter indicating repair will be made |
Timer Job Run Location | Any application server |
Default Schedule | Weekly on Sunday 12:00AM |
Repair Automatically | Yes |
Note: see this KB article released for an issue related to this health rule and resolved in the June 2011 cumulative update.
Search – One or more crawl databases may have fragmented indices
Assembly | Microsoft.Office.Server.Search |
Namespace | Microsoft.Office.Server.Search.Administration.Health.CrawlStoresMayBeFragmented |
What does Check() do? | Always returns fail status |
What does Repair() do? | Makes a call to [dbo].[proc_MSS_DefragGathererIndexes] |
Timer Job Run Location | Any application server |
Default Schedule | On Demand |
Repair Automatically | Yes |
Databases require upgrade or not supported
Assembly | Microsoft.SharePoint.Health |
Namespace | Microsoft.SharePoint.Administration.Health.DatabaseUpgradeRequired |
What does Check() do? | Checks if the DB has been flagged as “NeedsUpgrade” and is not “IsBackwardsCompatible” per build number |
What does Repair() do? | n/a |
Timer Job Run Location | Any application server |
Default Schedule | Daily at 12:00AM |
Repair Automatically | No |
Databases running in compatibility range, upgrade recommended
Assembly | Microsoft.SharePoint.Health |
Namespace | Microsoft.SharePoint.Administration.Health.DatabaseUpgradeRecommended |
What does Check() do? | For all SP databases check if the DB has been flagged as “NeedsUpgrade” but “IsBackwardsCompatible” per build number |
What does Repair() do? | n/a |
Timer Job Run Location | Any application server |
Default Schedule | Daily at 12:00AM |
Repair Automatically | No |
Databases within this farm are set to read only and will fail to upgrade unless it is set to a read-write state
Assembly | Microsoft.SharePoint.Health |
Namespace | Microsoft.SharePoint.Administration.Health.ReadOnlyDatabase |
What does Check() do? | For all SP content databases SELECT TOP 1 is_read_only FROM sys.databases WHERE database_id=XXX |
What does Repair() do? | n/a |
Timer Job Run Location | Any application server |
Default Schedule | Daily at 12:00AM |
Repair Automatically | No |
Content databases contain orphaned items
Assembly | Microsoft.SharePoint.Health |
Namespace | Microsoft.SharePoint.Administration.Health.ContentOrphans |
What does Check() do? | For all SP content databases count orphan SPSites, SPWebs, SPLists, SPItems via a call to [dbo].[proc_DetectOrphans] Orphans are objects where ParentID is null |
What does Repair() do? | For all SP content databases repair orphan SPSites, SPWebs, SPLists, SPItems via a call to [dbo].[proc_DetectOrphansFix] |
Timer Job Run Location | Any application server |
Default Schedule | Monthly on 1st of the month at 12:00AM |
Repair Automatically | No |
Some content databases are growing too large
Assembly | Microsoft.SharePoint.Health |
Namespace | Microsoft.SharePoint.Administration.Health.ContentDbsAreTooLarge |
What does Check() do? | For all SP content databases in each web application check if the DB is larger than 100GB |
What does Repair() do? | For all SP content databases found to be larger than 100GB Change the DB status to Offline for new SPSite creation |
Timer Job Run Location | Any application server |
Default Schedule | Hourly at 0 minutes past the hour |
Repair Automatically | No |
Database has large amounts of unused space
Assembly | Microsoft.SharePoint.Health |
Namespace | Microsoft.SharePoint.Administration.Health.DatabaseCanBeShrinked |
What does Check() do? | For all online SP databases not currently in a backup/restore process calculate the size of the DB and amount of unused space compare this with a threshold (internal) and AutoGrowth setting to determine if the DB can be Shrinked |
What does Repair() do? | For all DBs identified as can be Shrinked DBCC SHRINKDATABASE (0,{0}) |
Timer Job Run Location | Any application server |
Default Schedule | Weekly on Sunday at 12:00AM |
Repair Automatically | No |
Drives used for SQL databases are running out of free space
Assembly | Microsoft.SharePoint.Health |
Namespace | Microsoft.SharePoint.Administration.Health.SqlServerDrivesAreNearlyFull |
What does Check() do? | For all SP databases get drive space [sys].[xp_fixeddrives] and check space available [sys].[sp_helpfile] |
What does Repair() do? | n/a |
Timer Job Run Location | Any application server |
Default Schedule | Hourly at 0 minutes past the hour |
Repair Automatically | No |