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/2019 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/2019 databases
Following tasks should be included in your SharePoint Database Maintenance Job:
- Check Database Integrity Task
- Rebuild Index Task
- Update Statistics (Fullscan) Task
- History Cleanup Task
- Maintenance Cleanup Task
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:
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.
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):
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):
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):
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):
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):
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 them by pointing the green arrow to the next task. Double click the connector arrow and configure the Connection:
After connecting two tasks, the plan indicates the workflow between two tasks:
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:
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.
Microsoft
