Gradually reducing the Change Log for a Web Application

Wishv Prajapati

September 24, 2012

All Post

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"