Managing the WSS_Logging Database in SharePoint 2013

Introduction -
The logging database is a farm-wide repository of SharePoint 2013 monitoring information from every server in the farm. The logging database provides the option to view and customize various monitoring information in one place. Moreover, the logging database is the only SharePoint 2013 database for which you can customize reports by directly changing the database.

The default name of the database is SharePoint_Logging for SP 2013 and Wss_Logging for SP 2010.

Depending on logging configuration for SP2013 , logging database size will grow fast and huge.

To see the configuration :-
Go to Central Administration -> Monitoring ->"Configure usage and health data collection"  under Reporting
you will find what all things are selected for log , path for SP log and timer jobs.

Add caption

You will find on same page that two timer jobs used to import and to process the raw data in the database: 

1. Microsoft SharePoint Foundation Usage Data Import
2. Microsoft SharePoint Foundation Usage Data Processing

You can navigate to these timer jobs by this configuration page only  by clicking "Log Collection Schedule" link. OR by clicking Monitoring -> Review Jobs Definition -> filter with service name "Microsoft SharePoint Foundation Usage" and view "Service".

Ok this is the configuration but question arises here how can we check which log is taking more space because according to this information only we can get that which log need to remove or change its configuration.
Here we go....... Checking which log is taking more space

Login to your SQL Management Studio ->Expand the Databases section and find the WSS_Logging database. Right-click and choose Reports -> Disk Usage by Top Tables

You will get report like

Now we know which log is taking more space and need to change its configuration......So next topics is how to change identified configuration.

Change the Logging Settings for the WSS_Logging Database or few tables

By default the value for retention days for each log is 14. This means log file will be maintain for last 14 days.

By PowerShell commands we can check and change the value of retention days for the events selected on this database:

Check settings:
Get-spusagedefinition

Change settings:
Set-SPUsageDefinition 

To reduce the size of the logging database and to purge the old data from the logging database we can follow these steps.

Here we will discuss two ways to do this:- 

1) Remove identified log --- Navigate to this link Go to Central Administration -> Monitoring ->"Configure usage and health data collection"  under Reporting and,

 then  un-checked identified log and save it.But make sure that actually you want to remove or not because after this setting you will not get this log file until you will enable this again.

2)  Change it setting i.e. is its retention days -- By setting that it will keep log for that many day only and flush log before that.Please follow below steps to achieve this.

1. Check the retention’s days typing the PowerShell on a SharePoint 2013 Management Shell prompt as administrator:

2 .Now you can set retention days to any value less than current value to decrease size.

for e.g i want to set retention days for  "Task Use" to 7 then run below command

Set-SPUsageDefinition -Identity “Task Use” -DaysRetained 7

3.After that to cross check just run "Get-spusagedefinition" one more time.

4.After this you need to run the both timer jobs metioned above one by one.

5.Now shrink Wss_Logging database.

Thanks for reading my post ....please like or comment if any query related to this post.