SharePoint Configuration Database Too Big

Ever wondering why is the SharePoint configuration database so large?  The database is over 35 GB and its transaction log file grows up to 90 GB. There are potentially 2 issues.

1. the log file is too big: the log file can be shrank easily by using Management Studio, for example. Setting the database to simple mode can help too, but evaluate the pros and cons before making the change.
2. the database is still too big after shrinking the log file. After some research, I found out that there is a TimerJobHistory table in the configuration database and this table uses huge amount of space. There is a job "Delete jobs history" under Central Administration->Monitoring->Timer Jobs, which runs weekly by default, and that job is supposed to delete old entries from the timer job history table. In my situation, the table has entries more than one month old and default number of days to keep is 7, I believe. The timer job is enabled and there is no error messages. So what happened?

Long story short, the timer job runs weekly as expected, but it fails because during the execution, the transaction log runs out of space. To fix the problem, I need to reduce the amount of data being deleted by the timer job during each run, so I need to modify the daystokeephistory value of the timer job. However, there is no UI to modify the retention days, the only way is to use PowerShell. See below:

$test = Get-SPTimerJob | Where-Object {$_.name -eq "job-delete-job-history"}
$test.daystokeephistory

Above would display the current value.You can set the proper value for daystokeephistory then use either Central Administration or Power Shell to kick off the timer job, see below.

$test.daystokeephistory = 25
$test.update()
$test.runnow()

After multiple runs, at the end I set the timer job schedule to daily and daystokeephistory to 2 days.

So far so good.