SharePoint 2010 database maintenance

This is a reminder for SharePoint Admins, DBAs and Developers to ensure that they have taken the necessary steps recommended by Microsoft when it comes down to SharePoint 2010 database maintenance.

A few years ago, I posted out a similar ‘reminder’ for SharePoint (MOSS) 2007. http://www.jeremytaylor.net/2009/09/07/sharepoint-database-maintenance

More recently, last year, Microsoft (Bill Baer and Bryan Porter) published a document on SharePoint 2010 database maintenance. Continue reading to get a summary of what is involved in maintaining databases for SharePoint 2010…

Its important to note that in SharePoint 2010, there are a few Health Analyzer rules that automatically reduce index fragmentation for some of the databases in your SharePoint farm: The Farm Config Database, All Content Databases, User Profile SA Profile Database, User Profile SA Social Database, Web Analytics SA Reporting Database, Web Analytics SA Staging Database, Word Automation Services Database.

Here are the Health Analyzer rules:

They are set to run daily. If you click on ‘Run Now’ the rule would fire up a corresponding Stored Procedure on the SQL server.

For the above two Health Analyzer rules, the stored procs are: dbo.proc_DefragmentIndices and dbo.proc_UpdateStatistics

Anyway, coming back to Database Maintenance, Microsoft recommends the following in a nutshell:

  1. Once a week, out of business hours: DBCC CHECKDB to check for consistency to ensure that your data and indexes are not corrupted.
  2. Measure and reduce index fragmentation, especially the AllDocs table
  3. Under certain conditions that you need to be aware off, you need to disable the following Health Analyzer rules:
    Search – One or more property databases have fragmented indices
    Search – One or more crawl databases may have fragmented indices
  4. Monitor all other databases * (except the ones mentioned above) for fragmentation, and rebuild indexes within these databases when fragmentation exceeds 30%.
  5. Fragmentation up to 10% then reorganize (online), 10-75% – Rebuild (online), 75% – Rebuild (offline)
  6. Fine tuning index performance by setting a server wide setting of 80% fill factor
  7. Guidelines and steps for Shrinking data files if it CANT be avoided
  8. Step by step guide to creating SQL Server 2008 maintenance plans

 

* The following databases require you to manually monitor for fragmentation and index rebuild:

  • Secure Store Database
  • State Service Database
  • Profile Sync Database
  • Usage Database
  • Managed Metadata Database
  • Business Connectivity Services Database
  • PerformancePoint Services Database
  • Search Administration Database
  • Search Property Database (if you have disabled the Health Analyzer rule ‘Search – One or more property databases have fragmented indices’).
  • Search Crawl Database (if you have disabled the Health Analyzer rule ‘Search – One or more crawl databases may have fragmented indices’.
These are the default settings for the Property and Crawl Databases:

 

 

 

 

 

 

Curious to know more on why Microsoft mentioned about disabling these Health Analyzer rules? Then read more:

Click here to read the TechNet article

Click here to download the whitepaper docx / pdf / xps 

 

Leave a Reply

Your email address will not be published. Required fields are marked *