SharePoint Database Maintenance Recommendations


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. But prior to designing the tasks of the plan, you should take care about reporting/logging, so you can follow up on any issues or Problems.

 

Configure Logging and Reporting

SQL Server offers logging/reporting features, that  should be included in a SQL Maintenance Plan.

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

 

1. Task: 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. Task: 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. Task: 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. Task: 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. Task: Maintenance Cleanup Task

Why do we need this:
Use the Maintenance Cleanup Task to remove old files related to maintenance plans, including text reports (See: Logging and Reporting) 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)

 

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 (14)

  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

  3. Dirk Worsch says:

    Hi Christian,
    Thank you for this post, very helpful !
    Just one detail, can you fix the screenshot in Logging & Reporting. It points to the Subplan Schedule.

    Dirk

    1. Thank you for your feedback Dirk!

      By the way: I fixed the red marker in the Logging and Reporting picture 🙂
      This blog post should be renamed to The Screenshot Dilemma.

  4. UmrG says:

    Great post !
    In SharePoint 2010 Health Analyzer rules take care of the maintenance for the DBs listed below . Is this the same case with SP 2016 ? If yes , does that mean maintenance plan is needed for the database other then listed below and content databases in SP2016 ?
    •Configuration databases
    •Content databases
    •User Profile Service Application Profile databases
    •User Profile Service Application Social databases
    •Web Analytics Service Application Reporting databases
    •Web Analytics Service Application Staging databases
    •Word Automation Services databases

    Thanks .

    1. Hi Umar!
      Yes, these Health Analyzer rules are still in place and they do – like you have correctly written – maintain SharePoint databases. This is a reactive optimization though. The Health Analyzer rules detect if a database needs to get optimized and then trigger the maintenance by kicking off stored procedures.

      Having a maintenance plan in place proactively takes care that these Health Analyzer rules do not have to do reactive optimization, because the single tasks of the maintenance plan ensure, that database quality is always better than the thresholds the Health Analyzer rules use to start their work. It is also about having the control about what kind of maintenance is happening when and having the ability to check the results of it (logging and reporting of the maintenance plan).

      Hope this helps!
      Cheers, Christian

      1. UmrG says:

        Great , Thank you for the wonderful explanation for this. Did not think about it but it makes a great sense to be proactive, specially when it comes to SharePoint! Thanks.

  5. Rob Bright says:

    Great content! I see these issues a lot on SP risk assessments and I can’t stress enough how important it is to have a solid DB maintenance plan in place. Especially keeping the stats up to date….

    1. Thank you for this feedback Rob! Feel free to point to this blog article in your next RaaS Remediation Planning Session 😉

  6. Nice post.
    Can use as some kind of guide line.

Skip to main content