Safely reducing the Change Log for a Web Application
Content Databases by default contain logs of all change events for 60 days. If your Content Databases are under high loads, consider culling these logs.
In general, reducing really large database tables should be done gradually, to prevent Transaction Log failure due to lack of disk space, leading to failures and rollbacks.
There are two related tables that are trimmed through this operation:
dbo.EventCache
dbo.EventLog
I do not recommend reducing these to less than 30 days, or disabling them. That is because they seem to be used for two purposes by SharePoint itself. One is for User Alerts, the other is by the search incremental crawl, for detecting content changes.
In the script below, I reduce the log duration one day at a time, and wait a full 10 minutes after triggering the daily Change Log cleanup Timer Job to run.
the internal name of the timer job is “job-change-log-expiration”. You can get the list of the internal timer job names by using this CmdLet:
Get-SPTimerJob | select name
Or alternatively, pipe the Web Application into it:
get-SPWebApplication http ://SharePoint | Get-SPTimerJob | select name
Once you get the Timer Job, you can execute it:
$SubTJ = Get-SPTimerJob "job-change-log-expiration" -WebApplication $wa $SubTJ.RunNow()
Note that the duration of log retention is stored in type “Timespan”. The format in a string is actually “days.hours.minutes.seconds”, so you can change it to suit your needs.
$wa=Get-SPWebApplication http ://SharePoint
[TimeSpan]$OneDay=[timespan]"1.00:00:00"
[TimeSpan]$TargetDuration=[timespan]"30.00:00:00"
while ($wa.ChangeLogRetentionPeriod -gt $TargetDuration)
{
$wa.ChangeLogRetentionPeriod=$wa.ChangeLogRetentionPeriod-$OneDay
$wa.Update()
$SubTJ = Get-SPTimerJob "job-change-log-expiration" -WebApplication $wa
$SubTJ.RunNow()
Write-Host -ForegroundColor DarkGreen "Change Log Retention Period reduced by a single day to $($wa.ChangeLogRetentionPeriod.Days)"
Start-Sleep -s 600
}
Write-Host -ForegroundColor DarkRed "Already reduced the Change Log Retention Period to target"
