How to reduce the size of logging database OR How to purge the old data from Logging Database,


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

WSS logging database grows very fast and it cause the storage problem most of the time in sharepoint server 2010.

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.

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

Comments (16)

  1. Great article Manhar!

    I’m trying to understand why usage logs (logs with .Usage extension) are collected on the file system.  Does the timer jobs (Usage data import and Processing) imports into WSS_Logging  database from these files? If that is the case I guess these Usage logs need to be there until the timer jobs run.

    Also for MOSS 2007 – since usage reports are only good for 30 days is there any reason to keep historical usage logs? I’m seeing on one of the deployment number of years of historical usage logs taking up lots of disk space.  Is there any benefit keeping them?

    Many thanks for any input in advanced!

  2. Yeah USAGE files are created on file system from where timer job runs and import the data into logging DB and subsequently remove the usage files, so yeah you were correct log files should be there on the file system till the job runs. Moreover answer for your query why there is a need to write usage files on the file system rather to DB is that, writing usage to DB directly will be performance hit due to repeated round trips to the database.

    As far as MOSS in concern we know that timer job never use to clear the usage files instead it just use to append each line with an '&' to flag that the data has been read by the timer, not sure if there was a monthly job or something to purge the junk

  3. Thanks Yash – appreciate your feedback!

  4. Tobias Lekman says:

    Nice post. Strangely enough, setting day limits on disabled entries seemed to purge my database as well. To do this, I set all disabled retainers to one day using:

    Get-SPUsageDefinition | ForEach-Object { if ($_.Enabled -eq "False") { Set-SPUsageDefinition -Identity $_.Name -DaysRetained 1 }}

  5. Daniel Christian says:

    Hey Manhar,

    Thank you for taking the time to write this article.

  6. Ernesto Ono says:

    Hi, I would like to ask if my SQL Server Databse (WSS_Logging) are in AlwaysOn, is it work (to reduce size) ?

  7. goran says:

    i used your guide for reducing and purging wss_logging database. i changed retained period on 7 days, but when i start jobs Microsoft SharePoint Foundation Usage Data Import and Microsoft SharePoint Foundation Usage Data Processing, they finished succesfully but when i check wss_logging database size is not changed. the size of this database is 13GB.what i do?

  8. Asgard says:

    I have the exact same problem with SP 2013 as goran. Done all these things and disabled all logging but the database size stays 33 GB. Can I manually clear that DB on SQL Server?

  9. Dave S. says:

    Changing the settings changes how much data to keep, it does not shrink the data file.  After you have modified your SharePoint settings, use SQL Server tools to shrink the data file.

  10. Praveen says:

    Hi,

    Frequently SharePoint Central Admin DB size increasing double.  Can anybody suggest a solution to stop increasing DB size.

    Thanks

    Praveen

  11. Mikael says:

    Hi,

    I have an issue with the WSS_Logging database and its size. At first it had exploded to 250 GB, but managed to get that down to 70 GB by setting the DaysRetained to 1 and running the two timer jobs:

    Microsoft SharePoint Foundation Usage Data Import

    Microsoft SharePoint Foundation Usage Data Processing

    After that I also  unchecked these in central admin:

    Enable usage data collection

    Enable health data collection

    –> The problem is that even after a couple of days the database is still 70 GB (and yes I have checked from SQL server directly that there is no free space to shrink)

    The 'Disk usage by top tables' tells that there is dbo.TaskUsage_Partition18 which takesabout 55GB and Partition19 which takes about 15 GB

    –> select max(LogTime) from dbo.TaskUsage_Partition19;    gives 2015-01-26 07:13:42.550   which makes it three days now (as earlier stated daysRetained is set to 1)

    Any ideas why the db isn't cleaned?

    Regards,

    Mikael

  12. Jesus says:

    Hi Mikael, after change the retention period you have to reduce de WSS_Logging Data Base from the Sqlserver Management Studio.

    Regards,

    Jesus

    Great article Manhar

  13. Mikael says:

    Hi and thanks for your answer,

    The problem is that the retention doesn't remove the remaining 70GB (it removed data down from 250 to 70GB)

    –> So a shrink on the db won't help because there's nothing to shrink.

    Regards,

    Mikael

  14. Noel says:

    Hi, I have the same issue with Sharepoint 2013 SBMessageContainer_Log. Suddenly, it grows big and taking most of the disk size of DB server. Any solution would be appreciated.

  15. Ebonweaver says:

    Same issue here, setting the retention down to 7 days from 14, and lowering the max size, then running those jobs, did nothing.  In SQL the shrink indicates there is only 600mb of free space that would be recovered.  Considering the DB is 10gb and I told it the max (in SQL mgmt) could be 3gb, this clearly does not work.