Condividi tramite

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

  • Articolo

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

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

  • Anonymous
    August 02, 2012
    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!

  • Anonymous
    August 07, 2012
    The comment has been removed

  • Anonymous
    August 23, 2012
    Thanks Yash - appreciate your feedback!

  • Anonymous
    December 04, 2013
    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 }}

  • Anonymous
    January 15, 2014
    Hey Manhar, Thank you for taking the time to write this article.

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

  • Anonymous
    September 18, 2014
    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?

  • Anonymous
    October 21, 2014
    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?

  • Anonymous
    October 29, 2014
    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.

  • Anonymous
    November 18, 2014
    Hi, Frequently SharePoint Central Admin DB size increasing double.  Can anybody suggest a solution to stop increasing DB size. Thanks Praveen

  • Anonymous
    January 28, 2015
    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

  • Anonymous
    March 05, 2015
    The comment has been removed

  • Anonymous
    March 19, 2015
    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

  • Anonymous
    March 22, 2015
    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.

  • Anonymous
    January 13, 2016
    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.

  • Anonymous
    January 17, 2016
    Nice..