How to shrink an oversized sharepoint_config database

Objective

On the Sharepointserver, which has been implemented for more than 2 years, it is found that the Sharepoint_config database file is getting larger and bigger, has reached 90 GB, the contraction can reduce 20 GB, but will recover to 90 GB size after a week. is very strange.

Because of insufficient disk space, there are only 200GB sizes. There are a number of large and small sites to store photos, videos, documents and so on. So I decided to take a good look at the problem.

After unremitting Google, found the crux of the problem, and now share to everyone, hoping to meet similar problems, there is a reference.

　　Yes, attention. This refers to the database file, not the database log file. The assumption is that the log file is in the database management tool. Change the backup mode to be simple, the direct contraction is good!

1. Open Microsoft SQL Server Management Studio in 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 implementation. Is the size of each database table, and then see that the Timerjobhistory table has a very large number of rows and size.

3, Google has a similar solution, said to be due to the job-delete-job-history job execution failure, resulting in the job execution history can not be cleaned up in time. caused the configuration database to grow larger. So executing the following script will clean up the accumulation of a year, and the job is performed once a week by default. Clean up the history of last week's accumulated records.

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

$history. daystokeephistory = 365

$history. Update ()

$history. Runnow ()

4, after the completion of the implementation. Also change the Daystokeephistory value back to 7 days, for example:

5, can see the implementation, there is a job to do again. But I may have been here for more than 365 days. So it doesn't work, the job still executes failed. So just keep on Google and find a way out.

6, after unremitting search, found a PS script. Save the following script as a PS1 file. Execute it, it will work (at least I have worked here, will delete the redundant historical records);

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 very long time. I spent about 1 days here, the more than 90 GB of Sharepoint_config shrunk to 15 GB, the effect is very obvious, tidy up a very large part of the space. Do not always do disk space maintenance.

Attached after

Reference to the PowerShell command address: http://sharepoint.it-professional.co.uk/?p=228

How to shrink an oversized sharepoint_config database