HowTo: Shrink WSS_UsageApplication Database Size in Sharepoint 2013

Sharepoint2013_Shrink_WSSUsageApplication_Database.md

HowTo: Shrink WSS_UsageApplication Database Size in Sharepoint 2013

Ofiginal

1. Смотрим настройки периода хранения собранных событий разного типа

Add-pssnapin Microsoft.SharePoint.PowerShell
Get-SPUsageDefinition | ft -AutoSize

2. Устанавливаем время хранения для типов событий с временем хранения больше 3-х дней.

sequence
{
    Add-pssnapin Microsoft.SharePoint.PowerShell -ErrorAction Stop
    
    # Set maxinum days to store definitions data
    $MaxDaysToStoreDefinitionData = 13
    
    # Get definitions 
    $DefinitionsToProcess = Get-SPUsageDefinition | ?{$_.Retention -ge ($MaxDaysToStoreDefinitionData + 1)}
    Write-Verbose "SPUsageDefinitions that will be processed: " -Verbose
    $DefinitionsToProcess | Format-Table -AutoSize
    
    # set definitions retention policy
    ForEach ($Definition in $DefinitionsToProcess){
    	Write-Verbose "processing: $($Definition.Name)" -Verbose
    	try { Set-SPUsageDefinition -Identity $Definition.Name -DaysRetained $MaxDaysToStoreDefinitionData }
        catch { Write-Warning "ERROR while processing: $($Definition.Name)" -Verbose }
    }
    
    # check changed retention policy
    Write-Verbose "Lookup results:" -Verbose
    $DefinitionsToProcess | Format-Table -AutoSize
    
    # Start SPTimerJob to update sql database
    try { Get-SPTimerJob | Where-Object { $_.title -eq "Microsoft SharePoint Foundation Usage Data Import" } | Start-SPTimerJob }
    catch { Write-Warning "ERROR while processing: Start-SPTimerJob" -Verbose }
    
} #endsequence

3. После этого можно делать 'Shrink Database' базы WSS_UsageApplication средствами SQL Management Studio.