Skip Ribbon Commands
Skip to main content

KnowPoint

:

Chris McNulty > KnowPoint > Posts > Quick tip for tuning SharePoint 2013 Usage and Health data retention
March 23
Quick tip for tuning SharePoint 2013 Usage and Health data retention

Hi there. No clever photos or travel and weather updates today – just a PowerShell tip for SharePoint 2013.

In demo environments, it's fairly common for me to show Usage and Health based data. In SharePoint 2010 and 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. In addition, although you can groom the data retention for the actual log files in SharePoint central Administration, the most powerful tools are in PowerShell.

By default, those two Timer Service jobs are turned off. However, enabling the following two Timer jobs, the jobs will create appropriate SQL views in the logging database and start data collection. The default name for this database, in case you used the Farm Configuration Wizard ("White Wizard") is WSS_Logging.

  • Diagnostic Data Provider: Trace Log
  • Diagnostic Data Provider: Event Log

In SharePoint 2010, it was common for that database to equalize itself at 15-30GB after two weeks. However, in SharePoint 2013, there's a lot more data being aggregated. In one case, the WSS_Logging database hit 70GB after four days. Ouch! Probably don't need two weeks of data there, after all!

There are no shortage of scripts out there to show you how to configure these settings in SharePoint 2010. Here's a sample script that will set the retention period to three days:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

Set-SPUsageDefinition -Identity "Sandboxed Requests" -DaysRetained 3

Set-SPUsageDefinition -Identity "Content Import Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Workflow" -DaysRetained 3

Set-SPUsageDefinition -Identity "Clickthrough Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Content Export Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Page Requests" -DaysRetained 3

Set-SPUsageDefinition -Identity "Feature Use" -DaysRetained 3

Set-SPUsageDefinition -Identity "Search Query Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Site Inventory Usage" -DaysRetained 3

Set-SPUsageDefinition -Identity "Sandboxed Requests Monitored Data" -DaysRetained 3

Set-SPUsageDefinition -Identity "Timer Jobs" -DaysRetained 3

Set-SPUsageDefinition -Identity "Rating Usage" -DaysRetained 3

That's awesome if you like typing. And there are a lot more SPUsageDefinitions to set in SharePoint 2013. But PowerShell lets us get more compact. We can just use:

  • Get-SPUsageDefintion, which returns a full list of the configurable definitions
  • The pipe " | " which sends that output over to:
  • A ForEach-Object command, which will walk through that full list and reset DaysRetained to just one 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 back to 1 day.

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

Oh, and in large production environments its may be best to move that Usage and Health database to a separate physical database server so as not to impede production processing:

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

Have fun!

Comments

There are no comments for this post.