SharePoint Database Maintenance Recommendations


Database maintenance is a highly-neglected topic in daily SharePoint operation. It seems to be common sense that database backups must occur on a regular base, but only few customers realize the necessity to do some extra work on top of that. Main reason for not creating database maintenance jobs is missing SQL in-depth knowledge (what to do and why) and lack of time to care about it (don’t know how to set it up and where to find someone to help).

TechNet is highlining the importance of this topic in two articles (Database maintenance for SharePoint 2010 Products / Best practices for SQL Server in a SharePoint Server farm):

"Routine database maintenance is essential for the smooth operation of Microsoft SharePoint databases"

"(…) we strongly recommend implementing a scheduled maintenance plan from SQL Server (…)"

In Microsoft Premier Risk Assessments (RAP as a Service for SharePoint Server) a missing or incomplete Maintenance Job would result in an High Risk Finding.

Okay – I guess you get the point. This extra stuff that is supposed to add some database magic should be done. But anybody who is interested in setting up a SQL maintenance plan, most likely ends up in not finding good guidance that is up to date.

TechNet covers things to focus on - unfortunately in two separate articles. One article was written for SharePoint 2010 (Database maintenance for SharePoint 2010 Products), the second one explains best practices for SharePoint 2013 (Best practices for SQL Server in a SharePoint Server farm).

Basically, both articles are important and valid, but the SharePoint 2010 article includes some outdated information for SharePoint 2013/2016 environments (Database Fill factor recommendations have been different in SharePoint 2010). I decided to write this new article to have one step by step guide that possibly suits for most SharePoint farms.

 

What to include in the SQL Maintenance Plan for SharePoint 2013/2016 databases

Following tasks should be included in your SharePoint Database Maintenance Job:

  1. Check Database Integrity Task
  2. Rebuild Index Task
  3. Update Statistics (Fullscan) Task
  4. History Cleanup Task
  5. Maintenance Cleanup Task

SQL Server Maintenance Job for SharePoint Databases: All Tasks

Additional Notes:

  • This task sequence was validated by some of my great Microsoft colleagues
    (Thank you Andreas Maiwald, Rainer Asbach and Johann Hackl)
  • Database backup tasks could be added as well in theory. I recommend packaging all backup related tasks within a separate Maintenance Plan, for a more granular scheduling.

 

How to set up your Maintenance Plan

Use the SQL Management Studio to create your maintenance plan. Don’t use the wizard, the Maintenance Plan Editor gives you more control about your plan:

SQL Server Maintenance Job for SharePoint Databases: Create Plan

Manually add (drag & drop) the tasks from the toolbox pane into your plan and double click on each of them, to configure them properly.

 

1. Check Database Integrity

Why do we need this:
The Check Database Integrity Task checks the allocation and structural integrity of a database, by running the DBCC CHECKDB Transact-SQL statement.

How to configure this task (Example):

Check Database Integrity Task

Make sure to get familiar with this additional reading:
Check Database Integrity Task (Maintenance Plan)

 

2. Rebuild Index

Why do we need this:
We use the Rebuild Index Task dialog to re-create the indexes on the tables in the database with a new fill factor. The fill factor determines the amount of empty space on each page in the index, to accommodate future expansion. As data is added to the table, the free space fills because the fill factor is not maintained. Reorganizing data and index pages can re-establish the free space.

Important:
The Rebuild Index Task needs urgent attention in terms of scheduling. For best results, the Rebuild Index Task should be executed in offline mode. If the SharePoint environment is used across time zones (and there is no real maintenance slot during the night), the plan must be scheduled to run on the weekend. Consider creating a separate Rebuild Index Maintenance Plan that is scheduled for the Weekend. That way you can run other tasks on a daily (nightly) base. Learn about the difference between offline and online Index Rebuild: Database maintenance for SharePoint 2010 Products, Section: Online vs. offline index rebuilds

How to configure this task (Example):

Rebuild Index

Make sure to get familiar with this additional reading:
Rebuild Index Task (Maintenance Plan)

 

3. Update Statistics (Fullscan)

Why do we need this:
Query performance can significantly be improved by updating database statistics using the fullscan option.

There have been multiple Premier customers I support, who suffered dramatical performance issues caused by outdated statistics. This is what we want to avoid by creating a Maintenance Plan Task to update statistics with the Fullscan option. If the statistics of one of your content databases is in such a poor state, the CPU load of your SQL database instance is likely to hit 100%, causing serious problems for the whole farm. This is a known issue at Microsoft support and caused by outdated statistics.

Even though there are Timer Jobs / Health Analyzer Rules that are supposed to take care about just these statistics, there are circumstances where these jobs do not work properly. This is the reason why Microsoft explicitly recommends creating a task to update statistics using the fullscan option.

How to configure this task (Example):

Update Statistics Task

Make sure to get familiar with this additional reading:

 

4. History Cleanup Task

Why do we need this:
The History Cleanup Task discards old historical information from tables in the msdb database. This task supports deleting backup and restore history, SQL Server Agent Job history, and maintenance plan history.

How to configure this task (Example):

History Cleanup Task

Make sure to get familiar with this additional reading:
History Cleanup Task (Maintenance Plan)

 

5. Maintenance Cleanup Task

Why do we need this:
Use the Maintenance Cleanup Task to remove old files related to maintenance plans, including text reports created by maintenance plans and database backup files.

How to configure this task (Example):

Maintenance Cleanup Task

Make sure to get familiar with this additional reading:
Maintenance Cleanup Task (Maintenance Plan)

 

Logging und Reporting

There are logging/reporting features available, that are useful to include in a SQL Maintenance Plan.
After designing the tasks of your plan, consider adding some reporting:

SQL Server Maintenance Job for SharePoint Databases: Logging and Reporting

SQL Server Maintenance Job for SharePoint Databases: Logging and Reporting, Options

Make sure to get familiar with this additional reading:
SQL Server Maintenance Plans Reporting and Logging

 

Connecting the Tasks of the Plan

Once you added all tasks and configured them individually, you must connect them all together:

Connect Maintenance Plan Tasks (1)

Connect them by pointing the green arrow to the next task. Double click the connector arrow and configure the Connection:

Connect Maintenance Plan Tasks (2)

After connecting two tasks, the plan indicates the workflow between two tasks:

Connect Maintenance Plan Tasks (3)

 

Scheduling the Maintenance Plan

Customers usually schedule Maintenance Plans for SharePoint databases on a daily/weekly base (depending on the runtime). Plans must be aligned with other maintenance tasks like backup Jobs and are scheduled to run in off peak hours (during the night, at the weekend)

Important:
The Rebuild Index Task needs urgent attention in terms of scheduling. For best results, the Rebuild Index Task should be executed in offline mode. If the SharePoint environment is used across time zones (and there is no real maintenance slot during the night), the plan must be scheduled to run on the weekend. Consider creating a separate Rebuild Index Maintenance Plan that is scheduled for the Weekend. That way you can run other tasks on a daily (nightly) base.
Learn about the difference between offline and online Index Rebuild: Database maintenance for SharePoint 2010 Products, Section: Online vs. offline index rebuilds

 

Test the Maintenance Plan

Carefully evaluate the Maintenance Plan on your Test Environment for a period of time (2-4 weeks), using complete copies of your production databases. Check the overall runtime and make sure the plan does not collide with other scheduled maintenance. Backup Jobs of transaction logs are not affected.

As soon as you have insight about overall runtime and are familiar with the necessary configuration of the maintenance plan on your Test environment, create the Plan on your productive environment.

 

Check the results of the Maintenance Plan

Usually you check the results of your database maintenance using following two options:

1. Check Maintenance Job History
Right click the Maintenance Job in SQL Management Studio and choose the View History Option:

SQL Server Maintenance Job for SharePoint Databases: Job History

 

2. Check Maintenance Job Log File
In the log file folder (in the root folder of the SQL instance) there is a log file for each Maintenance Job. This can be reviewed and checked.

Further Reading: SQL Server Maintenance Plans Reporting and Logging

 

SQL Server Jobs vs. Maintenance Plans

I want to explicitly point out, that experienced database admins can of course use SQL Server Jobs instead of creating a SQL Server Maintenance Plan. SQL Server Jobs add further flexibility and are often used in SQL Server Always On scenarios. The steps to include in the job match the steps described above.

Further reading:

 

What you must not do

  • Shrinking databases on a regular base. Just don't do it. Shrink manually if you really have to, but never include it in a Maintenance Plan. Further Reading: Shrinking data files
  • Run Maintenance Plan during working hours. Don't do it. Performance is likely to be degraded during the single steps of the Maintenance. Review the Scheduling the Maintenance Plan section of this article to learn more.

 

Conclusion

SQL Database Maintenance is no rocket science. It is easy to set up and creates performance and reliability benefits. Make sure to set up a Maintenance Plan on all of your SharePoint farms.

 

Final Note

Please share your thoughts & own experiences in the comments. Any feedback or additions for this post is highly welcome.

Thank you for rating this article if you liked it or it was helpful for you.

 

Comments (6)

  1. Jean Marie THIA says:

    hi Christian,
    All the answers to my quest for best practices in SharePoint 2013 databases maintenance. Great post !
    Just one detail, the update statistics screenshot is not the right one.
    Jean Marie

    1. Thank you for your Feedback Jean Marie! I fixed the screenshot.

      1. Jean Marie THIA says:

        I should have wait until I went through all the steps! The maintenance cleanup task screenshot is also wrong 😉

        1. Thank you again. Fixed it 😀

  2. Great article thanks a lot i will share it

Skip to main content