SharePoint – ConfigDB – Growing

Alright, so one of my developers was complaining that his config database on his development environment was more than 40GB(If your config database is larger than 10GB, you should continue reading), or actually it was the sysadmin that complained, but my developer was targeted. He asked if I could have a look at it. Sure of course, properly transaction logs gone wild…. So logging on the server, finding the trans logs were all good. All right further digging came up with the TimerJobHistory table being filled with around 100 million records. Alright, now what?

Some posts have some good Powershell scripts, that will incrementally delete your timerjob history, maybe also change your retention time. Sure I started to run those, but that didn’t quite do the trick. So what was wrong then?

Alright here is the deal. The “Delete Job History” job, has a timeout of 5 minutes. That means it will delete records, but if the delete is not completed within 5 minutes, it throws a timeout. It actually fails the timer job. (Timeout) This timer job is set to run only once a week. So guess what happens when you create more timer jobs per week than the Delete Timer job can remove in 5 minutes? – Yup, the TimerJobHistory table will grow…

I found the easiest fix, just to change the schedule for the “Delete Job History”. For the 100 million records, I changed it to every 7’th minute. I know that it will not run longer than 5 minutes, so a 2 minutes slack should be sufficient. So what should the setting be? Adjust according to your environment.

Summary
SharePoint ConfifDB growing beyond the normal 4-8GB. The data file, not the log file.

Root cause
The Delete Job History cannot delete more rows than created timer jobs per week within the 5 minute timeout. This can be related to heavy deployment or insufficient resources.

Solution
Change the schedule of the Delete Job History to run more frequently.

Powershell
Get-SPTimerJob job-Delete-Job-History | Set-SPTimerJob -Schedule “daily at 05:00:00”

Which interval is required depends on your environment or the amount of rows in backlog. So set schedule accordingly and following the types for Set-SPTimerJob.
The type must be a valid SharePoint Timer service (SPTimer) schedule in the form of any one of the following schedules:
– Every 5 minutes between 0 and 59
– Hourly between 0 and 59
– Daily at 15:00:00
– Weekly between Fri 22:00:00 and Sun 06:00:00
– Monthly at 15 15:00:00
– Yearly at Jan 1 15:00:00

Advertisements

One comment

  1. Thank you for the article but I already concluded the same reasoning for our situation before reading this just to confirm. The ConfigDB grew to 190GB, TimerJobHistory table with 376 million records. I am down to 141 million records after a day of running the job every 10min.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s