Reduce the Size of Usage and health database of SharePoint 2013 as SQL CPU load is high

Reduce the Size of Usage and health database of SharePoint 2013 as SQL CPU load is high

******************************************************************************************************************************

SharePoint Usage logging database grows very fast and it cause the storage problem most of the time in sharepoint server 2010/2013.

To reduce the size of the logging database or to purge the old data from the logging database we can these steps. 
To find the Logging Database Name go to following path:-
Login to Central Administration -> Monitoring -> Configure Usage and health data collection->
 

The Logging database stores the following things:-

• ULS Logs from 14 Hive\LOGS
  
• Raw Resource Usage Data
  

 This data is used by Web Analytics in Sharepoint. Two timer jobs Microsoft SharePoint Foundation Usage Data Import and Microsoft SharePoint Foundation Usage Data Processing are used for importing and processing the raw data in the web analytics database.
By Default the system retains 14 days of Usage data in this database. By following PowerShell command we can check and change the value of retention days of this database. Get-spusagedefinition and Set-SPUsageDefinition  
Try  Get-SPUsageDefinition

 Now you need to find which table is taking most of the space inside the WSS logging Database. 

You Can check the same from the SQL Server
Login to Sharepoint Management Studio -> Select your logging Database (Right Click) -> Reports- > Standard Reports -> Disk Usage by Top Tables.
 

 Say RequestUsage* is taking most of the space inside WSS Logging database so  you can bring down the retention period of  Page Request Event.
Or if you want you can bring down the retention period for all the Events for any number of days from 14 to 1. 
Use the following command to do so.

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

Run the same Power Shell command again to cross check "Get-SPUsageDefinition" and check the Retention period is changed or not.
 

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.
And it will take you to the timer jobs.
 

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


Once the timer jobs is run you can check and confirm database has released the space.