How to reduce the size of logging database by purging old data from it.
WSS logging database grows very fast and it may cause storage problem at times SharePoint server 2010.
More on Logging Database?
The Logging database stores the following data:-
· ULS Logs from 14 Hive\LOGS
· Raw Resource -Usage Data
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’
To find the Logging Database Name go to following path:-
Login to Central Administration -> Monitoring -> Configure Usage and health data collection->
How to purge old data from logging database
=>Please try below steps to purge old data from the logging database and reduce the size of database.
Step1: Run the powershell command: ‘Get-SPUsageDefinition’
Step 2: Now we 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.
Step 3: Use the following command to purge the old table data from this database.
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.
Step 4: 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.
Click on both the Job Definitions one by one and hit 'Run Now' to run the timer jobs
Once the timer jobs gets completed we can check if database have released the space,
------------------------------------------------------------------------------------------------------------------------
Please Note :
Though there is no such performance impact of running this timer job on sharepoint server or on the SQL server but it is suggested please run it in off business hours.
Once the timer jobs is run you can check and confirm database has released the space.