How to reduce the size of logging database OR What 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 Hive\logs

• Raw Resource Usage Data

This data is used by Web Analytics in Sharepoint. Both timer jobs Microsoft SharePoint Foundation usage data Import and Microsoft SharePoint Foundation usage data processing is used for importing and processing the raw data in the Web Analytics database.

By Default the system retains for the Usage data in this database. By following PowerShell command we can check and change the value of retention days in this database. Get-spusagedefinition and Set-spusagedefinition

Try get-spusagedefinition

Now your need to find which table was 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 report S-Disk Usage by Top Tables.

Say requestusage* is taking most of the space inside WSS Logging database so can bring the retention period of Page Request Event.

Or If you want your can bring down the retention period for all the Events for any number of days from 1.

Use the following command-to-do.

Set-spusagedefinition-identity "page Requests"-daysretained 3

Run the same Power Shell command again to cross check "Get-spusagedefinition" and check for the Retention period is changed or Not.

After, we need to run the both 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 Collect Ion Schedule.

And it'll take your to the timer jobs.

Now Click on both the JOB definitions one by one and hits ' Run Now ' to run the timer jobs

Once the timer jobs is run-can check and confirm database has released the space.

http://blogs.msdn.com/b/manhar/archive/2012/04/17/ How-to-reduce-the-size-of-logging-database-or-how-to-purge-the-old-data-from-logging-database.aspx