Issues related to SharePoint Configuration Database

Technorati Tags: SharePoint Configuration Database Size

We were facing all kinds of strange issues which we could not explain. We saw Time-Out errors pointing to SQL. We were no longer able to create for example new Search Service Applications (Timed-Out), but also deleting them after was not possible. Attempts were done via GUI, PowerShell and STSADM but none of them worked.

Commands used for example:

$spapp = Get-SPServiceApplication -Name "Search Service Application - TEST"
Remove-SPServiceApplication $spapp -RemoveData

Get-SPServiceApplication –Identity

stsadm -o deleteconfigurationobject -id "xxx Identity"

We changed time out values for:

Changed App Pool Time out-values
Changed Central Admin Site Time Out Value
Changed webconfig file - added execution time out value

stsadm -o setproperty -pn database-connection-timeout -pv 900

But none of the above solved the issue until we found out that the SP Config DB was extremely big. When looking at the tables we saw this one:

When looking at the properties of this specific table and checking the storage used, the amount of data was extreme and the number of row counts was more than 110 million!!!

What was wrong?

There is a weekly timer job (by default) scheduled once a week which cleans up the Timer Job History for 7 days. This timer job was actually failing, it runs for about 5 minutes and a bit and then failed. It actually means that the amount of data in your DB just keeps on growing and growing.

First i found some PS scripts which i ran manually like below:

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

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

So you have to change the values for the number of days you want to keep the history. Remember that to be able to run the job successfully you will have to check the dates in the table

So within SQL: use the Select Top 1000 Rows on the table and check the first dates you will see. If that date is for example 250 days ago , then change the value in the above script to 250 and make sure the script runs successfull.

When running the scripts, the log file will quicly start growing…

Later we found a very nice PowerShell script, thanks to Andy Rollings: http://sharepoint.it-professional.co.uk/?p=228

By using the below script, you start with your first date and just keep on monitoring in CA whether the jobs run successfull

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"

 

After the jobs have run (for me it took around 160 times!), we saved 82 GB on data. After shrinking the SP Config DB (data and logs), all issues were solved. no time-outs anymore, service applications could be created and removed……

4 weeks of troubleshooting solved!

 

Technorati Tags: SharePoint 2013,Timer Jobs,SharePoint Configuration Database,Timer Job History Deletion