How to shrink a large sharepoint_config database

Objective

On a SharePoint server that has been running for more than 2 years, it is strange to find that Sharepoint_config's database files are getting bigger and larger and have reached 90 gigabytes, shrinking by 20 gigabytes, but recovering to 90 gigabytes of size a week later.

Because of insufficient disk space, there are only 200GB size, there are a number of large and small sites to store photos, videos, documents, etc., so decided to study this problem.

After unremitting Google, found the crux of the problem, and now share to everyone, hope to meet similar problems of people, have a reference.

　　Yes, attention. Here refers to the database file, not the database log files, if it is a log file, in the database management tool, modify the backup mode as simple, directly contracted just fine!

1. Open Microsoft SQL Server Management Studio in the database server, locate the Sharepoint_config database, and then execute the following statement:

exec sp_msforeachtable @command1 = "exec sp_spaceused '?"

2, see the results of the execution, is the size of each database table, and then see timerjobhistory the table has a large number of rows, the size is very big.

3, Google has a similar solution, said is because job-delete-job-history this job run failure, resulting in the job run history can not be cleaned up, resulting in a larger configuration database. So running the following script will clean up the accumulation of a year, and the job defaults to run once a week to clean up the history that was accumulated last week.

$history = Get-sptimerjob | Where-object {$_.name-eq "Job-delete-job-history"}

$history. daystokeephistory = 365

$history. Update ()

$history. Runnow ()

4, after the completion of operation, but also to change the Daystokeephistory value back to 7 days, such as:

5, you can see the run, there is a job to run again, but I may be here because it has been more than 365 days, so there is no effect, the job is still running failed. So we can only continue to Google, looking for solutions.

6, after unremitting search, found a PS script, the following script, saved into a PS1 file, execution, will play a role (at least I have worked here, will delete the redundant history);

add-PSSnapin Microsoft.SharePoint.PowerShellWrite-host"clearing down Timer Job history"$daysToKeep= 300$daysToPurgeInOneLoop= 5 while($daysToKeep -GT0){$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. Namewrite-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 the row counts on dbo. Timerjobhistory Table in Config DB to ensure run complete"

Conclusion

Of course, the command to execute PowerShell takes a long time, I use about 1 days here, the more than 90 GB of Sharepoint_config shrunk to 15 GB, the effect is very obvious, tidy up a lot of space, do not often do disk space maintenance.

Attached after

The PowerShell Command reference address: http://sharepoint.it-professional.co.uk/?p=228

How to shrink a large sharepoint_config database