Reduce the size of SharePoint 2013 Usage and Health database

How to reduce usage and health data collection database in SharePoint?


Run the powershell command: ‘Get-SPUsageDefinition’

This command returns a usage definition object. The default data retention period is 14 days.

Separately change retention for some definition

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

Set-SPUsageDefinition -Identity "Sandboxed Requests" -DaysRetained 3

Set-SPUsageDefinition -Identity "Content Import Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Workflow" -DaysRetained 3

Set-SPUsageDefinition -Identity "Clickthrough Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Content Export Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Page Requests" -DaysRetained 3

Set-SPUsageDefinition -Identity "Feature Use" -DaysRetained 3

Set-SPUsageDefinition -Identity "Search Query Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Site Inventory Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Sandboxed Requests Monitored Data" -DaysRetained 3

Set-SPUsageDefinition -Identity "Timer Jobs" -DaysRetained 3

Set-SPUsageDefinition -Identity "Rating Usage" -DaysRetained 3


Command for all definitions

Get-SPUsageDefinition | ForEach-Object {Set-SPUsageDefinition -Identity $_.name -DaysRetained 3}



Once that's finished, Get-SPUsageDefinition command should confirm that everything has been set back to 3 day.

SharePoint:

After that we need to run the two timer jobs to clean the old data 'Microsoft SharePoint Foundation Usage Data Import' and 'Microsoft SharePoint Foundation Usage Data Processing'.

Go to Sharepoint Central Administration -> Monitoring -> Configure Usage and health data collection-> Log Collection Schedule.

Click on both the Job Definitions one by one and hit 'Run Now' to run the timer jobs.

SQL:

After that, you can use SQL Management Studio to shrink the database back to a more manageable size on disk.



That's it!