[The blog post below is based on the information collected by Derek Smay and Alexander Peter, two Microsoft Premier Field Engineers located in the US]
One of the changes in the November 2017 CU for SharePoint 2013 affects the maintenance of the [dbo].[TimerJobHistory] table in the SharePoint configuration database, which can experience significant growth if it is not trimmed on a regular schedule.
Historically SharePoint 2013 preserved the timerjob history for all timerjobs which ran in the last 7 days. Starting with November 2017 CU for SharePoint 2013 (build 15.0.4981.1002), the maintenance of the [dbo].[TimerJobHistory] table has been updated in a way that it will never keep more than 10,000 job records.
To maintain the [dbo].[TimerJobHistory] table SharePoint only relied on the Delete Job History timer job, which is responsible to purge records older than 7 days. The default schedule is to run once a day but it is possible to change the schedule by updating the frequency in the Central Administration Site. The Delete Job History timer job, invokes the [dbo].[proc_DeleteOldTimerJobHistory] stored procedure which does the actual work. If this timer job is not enabled or does not complete successfully, the [dbo].[TimerJobHistory] table can grow to hundreds of millions of records and hundreds of Gigabytes, which can impact performance and availability.
With November 2017 CU, an additional stored procedure, [dbo].[proc_DeleteOldTimerJobHistoryByRows], was added, which limits the number of rows in the [dbo].[TimerJobHistory] table table to 10,000 records. Be aware that both of these stored procedures actually maintain three different tables: [dbo].[TimerJobHistory], [dbo].[TimerScheduledJobs] and [dbo].[TimerRunningJobs]. Of these three, the largest table is [dbo].[TimerJobHistory].
The new stored procedure is not called from any specific timerjob – it is executed as the final step of each single timer job running on your system. That means the maintenance of the [dbo].[TimerJobHistory] does no longer rely the successful execution and on proper schedule of a single timerjob.
This is the logic built into the execution of SharePoint timer jobs after November 2017 CU:
- Execute the logic of the timer job including the execution of one or more stored procs to complete intended task of the timer job
- Execute the [dbo].[proc_AddTimerJobHistory] proc to document the execution of the completed timer job to the [dbo].[TimerJobHistory] table
- Execute the [dbo].[proc_DeleteOldTimerJobHistory] proc to limit the number of records in [dbo].[TimerJobHistory] table to a maximum of 10,000 by removing the oldest records.
This new enhancement eliminates the possibility of the SharePoint configuration database becoming bloated. By imposing a ceiling on the [dbo].[TimerJobHistory] record count, a number of benefits are recognized such as disk space consumption for the database and its backups, I/O activity, backup impact on network throughput as well as maintenance activities (i.e. CHECKDB, Index, etc).
After running November 2017 CU you should never have to worry about [dbo].[TimerJobHistory] to exceed 10,000 rows. And indeed, this will clearly keep this table in check BUT be careful if there was a problem with the maintenance of your [dbo].[TimerJobHistory] table before installing of November 2017 (or later) CU:
After installing November 2017 CU each and every timerjob will be responsible to maintain the size of the [dbo].[TimerJobHistory] table. In case that the [dbo].[TimerJobHistory] had several million rows due to the fact that the Delete Job History timer job was disabled or did not work correctly the new maintenance stored procedure will take a very, very long time to complete. As several SharePoint timerjobs will start and end each second that will cause this stored procedure to run in parallel on your SQL server in multiple different SQL sessions.
In this case you can see your SQL server to run on high CPU on each of the available cores till the [dbo].[TimerJobHistory] has been trimmed to the new maximum size.
To prevent this from happening you should ensure that the the Delete Job History job has been running successfully. If you have a SharePoint Config database that is large in size, check the row count and size of [dbo].[TimerJobHistory] table.
The code change introduced in November 2017 CU for SharePoint 2013 is not included in SharePoint 2016 and is currently not planned to be ported to SharePoint 2016. The reason is that the maintenance of the [dbo].[TimerJobHistory] table in SharePoint 2016 is done differently than in SharePoint 2013. E.g. the Delete Job History job does bulk deletes of 1000 records rows rather than on row after the other and it only keeps records for 3 days compared to 7 days in SharePoint 2013.
But be aware that the proper maintenance of the the [dbo].[TimerJobHistory] table in SharePoint 2016 requires that the Delete Job History job runs successful on a regular basis.