SharePoint Config Database Growth

Hi,

Not sure if you are aware of this but every time a timer job runs in SharePoint it creates a record of this in the TimerJobHistory Table stored into the config DB of your SharePoint farm.

SharePoint has an internal timerjob hidden from the Central Admin screen that is supposed to clear this table down once a week, leaving at most 7 days of records.

However if for some reason you build up to many records in this table you can exhaust your transaction log on the config DB and the clear down job then fails.

Once this start’s to happen it can go un-noticed for a while, and the records contiue to build up, your DBA might see an issue every week in the SQL logs that the transaction log has filled up on the config DB, but unless they know sharepoint by the time they check the job will have failed and released the TLog space so the TLog will look normal again.

However the problem will then compound itself, as the job’s default schedule is to run weekly, so if it you exhausted the TLog this week, then next week it will have even more records to delete and the same will occur.

To resolve this issue you can do one of two things.

Increase the amount of TLog space available so the weekly job can clear down. However if you have your databases on AutoGrow the first time you might be aware of this problem is when you run out of disk space on the volume holding the Config DB TLog, you may also notice that the amount of space your config DB is consuming starts to get larger and larger.

The second thing is to change the setup of the Clear down job so that it doesn’t attempt to delete to may records in one go, this can be done via powershell, not CA as the job is hidden from the CA screen.

Find below my powershell solution to the problem it runs the job taking 5 days of history at a time and deleting it.

Finally it sets the retention on the job to 3 days, and the schedule to run daily so that hopefully we won’t have the problem again.

Check out the script below and see if it can help you if you have this problem.

Two parameters you can modify,

$daysToKeep = 730
$daysToPurgeInOneLoop = 5

$daysToKeep

Use this to set how far back in days the job will start at, this is designed so you can go back and clear up historical jobs that haven’t been cleared for some time

$daysToPurgeInOneLoop

Use this to set how many days are purged on each iteration, you’ll need to adjust this to make sure you don’t overwhelm the config DB transaction log size you have setup, or run your storage out of space if your TLog is set to AutoGrow.

cls
Write-Host “Clearing Down Timer Job History”
$daysToKeep = 730
$daysToPurgeInOneLoop = 5

while ($daysToKeep -gt 0)
{
$history = get-sptimerjob | where-object {$_.name -eq “job-delete-job-history”}
Write-Host ” ”
Write-Host -NoNewLine “Setting Days to Keep:”
Write-Host -ForegroundColor Green $daysToKeep
$history.DaysToKeepHistory = $daysToKeep
$history.update()
Write-Host -ForegroundColor Green “Starting Purge Job”
$lastTimeJobRan = $history.LastRunTime
$history.runnow()
Write-Host -NoNewLine -ForegroundColor Green “Waiting For Purge Job to Complete”
$jobFinished = $false
while ($jobFinished -eq $false)
{
Start-Sleep -Seconds 2
$runningJob = Get-SPTimerJob $history.Name
Write-Host -NoNewLine -ForegroundColor Yellow “.”
if ($lastTimeJobRan -ne $runningJob.LastRunTime)
{
$jobFinished = $true
}
}
Write-Host ” ”
Write-Host -ForegroundColor Green “Ending Purge Job”
$daysToKeep = $daysToKeep – $daysToPurgeInOneLoop
}

Write-Host -ForegroundColor Green “Setting Final Job History Retention to 3 days, and schedule to run daily @ 5am”
$history.DaysToKeepHistory = 3
$history.update()
$history.runnow()
Set-SPTimerJob -Identity $history -Schedule “Daily at 05:00”
Write-Host -ForegroundColor Yellow “Please check row counts on dbo.TimerJobHistory Table in Config DB to ensure run complete”

Bookmark the permalink.

3 Responses to SharePoint Config Database Growth

  1. Graham Hollingsworth says:

    Great stuff!

  2. Brilliantly worked. Had a history job table with more than a billion records. Which ever way I tried to manipulate it, I recieved a timeout. Your gradual timejob purge did the trick!

  3. Paul M says:

    Great script! Thanks!!

Leave a Reply

Your email address will not be published. Required fields are marked *


*