How to reduce the size of logging database and how to purge the old data from Logging Database

Depending what you have selected to log on SharePoint 2013 logs, the WSS logging database grows very fast and it cause the storage problem most of the time.

In SharePoint 2013, you can selectively collect the ULS and Windows event logs from all the servers in a farm to a central Usage and Health database. The default data retention period is 14 days and bellow, I have my own sample of events selection:

clip_image001

The default event log configuration is:

eventlogs

 The Logging database stores the following things:

  • ULS Logs from 15 Hive\LOGS
  • Raw Resources Usage Data

clip_image002

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

clip_image003

clip_image004

By following 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.

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

Get-spusagedefinition

clip_image005

2. Find the Logging Database Name:

Login to Central Administration -> Monitoring -> Configure Usage and health data collection:

clip_image006

Check the name in the “Database Name” of the “Logging Database Server” section:

clip_image007

3. 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 SQL Server Management Studio -> Select your logging Database (Right Click) -> Reports- > Standard Reports -> Disk Usage by Top Tables.

clip_image008

Our report says that the “dbo.RequestUsage_*” are taking most of the space inside WSS Logging database, so  you can bring down the retention period of  Page Request Event from 14 to 5 for example.

clip_image009

4. By following the PowerShell below, you will bring down the retentions period of the “Page Requests” from 14 to 5:

Set-SPUsageDefinition -Identity “Page Requests” -DaysRetained 5

clip_image010

5. Run again the PowerShell to check the new value was changed or not:

Get-spusagedefinition

clip_image011

6. 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:

clip_image003[1]

It will take you to the “Job Definitions” page:

clip_image004[1]

7. Now Click on both the Job Definitions one by one and hit ‘Run Now’ to run the timer jobs:

clip_image012

clip_image013

8. Check if the timer jobs have run with success for all servers of the farm:

clip_image014

If you prefer, you can run those jobs through this PowerShell:

Get-SPTimerJob | Where-Object { $_.title -like “*usage data*” } | Start-SPTimerJob

9. Once the timer jobs had run with success, you can check and confirm database has released the space. Note that now, I don’t have “dbo.RequestUsage_Partition4” and “dbo.RequestUsage_Partition3”. It have reduced almost 5GB on my storage.

clip_image015

Alternatively, you can choose bring down all definitions at once time. Below a sample to bring down all definitions to 1 day:

Get-SPUsageDefinition | ForEach-Object {Set-SPUsageDefinition -Identity $_.name -DaysRetained 1}

Once that’s finished, a plain Get-SPUsageDefinition command should confirm that everything’s been set to 1 day.

clip_image016

After that, you can use SQL tools to shrink the database back to a more manageable size on disk. (Reminder, you may not need to have the WSS_Logging database in fully-logged, full recovery mode, which generates larger SQL transaction logs. Simple recovery mode usually works best here and it is the MS recommendation).

You may consider in a large production environments that the best option is to move the Usage and Health database to a separate physical database server using the Powershell below or at least have a separated disk to host this database:

Set-SPUsageApplication -DatabaseServer <DatabaseServerName> -DatabaseName <DatabaseName> [-DatabaseUserName <UserName>] [-DatabasePassword <Password>] [-Verbose]

References:

http://technet.microsoft.com/en-us/library/jj715694.aspx

http://technet.microsoft.com/en-us/library/cc678868(v=office.15).aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s