How to reduce the size of logging database by purging old data from it.

How to reduce the size of logging database by purging old data from it.

  • Comments 1

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->

clip_image002

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’

clip_image004

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.

clip_image006

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.

clip_image008

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.

clip_image010

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

clip_image012

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.

 

POST  BY :Manhar Sharma [MSFT]


Leave a Comment
  • Please add 3 and 4 and type the answer here:
  • Post
  • Hi, ¿Is it work if my SQL Server database are in AlwaysOn?

Page 1 of 1 (1 items)