Understanding and Managing the WSS_Logging Database in SharePoint 2013

Posted on March 11, 2014 by Michael Simmons in SharePoint with 0 Comments

If you’ve been managing SharePoint for a while, you’ve undoubtedly come across the WSS_Logging database, one of the system databases used by SharePoint 2013 to keep track of the logs that are generated from across a SharePoint farm. It’s also part of the SharePoint Usage and Health Monitoring service, holding information from all servers in the farm to help with reporting on the overall health of the farm. If you’re receiving notices about “critical errors in your farm” when you log into Central Administration, you’re reaping the benefits of that WSS_Logging database.

Why Bother with Log Files on the Hard Drive?

Just because there is a database doesn’t mean that it is best to have all of the SharePoint servers constantly writing data into the database. Even in a small farm, the log files can stay very active with hundreds of events being written to the logs in just a few minutes. To keep the database from being in constant use, the SharePoint servers keep their own log files on them and a timer job is used to ship the log files into the database.

WSS_Logging Database: Space Issues

Because all of the logs from all of the servers in your farm end up being brought into the WSS_Logging database, it can quickly balloon in size. It isn’t unheard of for a WSS_Logging database to be one of the largest consumers of hard drive space on your SQL Server. If you’re WSS_Logging database is growing too big, you might want to reduce its size by controlling how much data you’re saving into it from the ULS logs.

It can be helpful, but not required, to identify which logs are taking up the most space in the WSS_Logging database and then use that information to help guide your decisions about what to keep. If determining what to keep is more about business decisions or you have an external requirement to keep specific log entries in the WSS_Logging database for a set time, obviously it doesn’t matter what the size is. So here’s how to check the WSS_Logging database to see what’s taking up the space. If you find that a large amount of data is being taken up by something that doesn’t provide a lot of business value to you, then I’ll show you how to use PowerShell to make all of your WSS_Logging database dreams come true!

Sponsored

Determine Which Logs Take Up the Most Space

  • Log onto the SQL Server or otherwise make your connection to it through SQL Server Management Studio. Don’t forget that if you have your SQL Server setup to use a nonstandard TCP/IP port, then you’ll want to connect to it by formatting your server name to include the server, instance name and the port (like “SERVER\INSTANCENAME,PORT#”).
  • Now that you’re connected to the server, expand the Databases section and find the WSS_Logging database. Right-click and choose Reports -> Show Tables by Usage.

wss_logging sharepoint get usage

Each of the tables represents one day’s worth of logs from one of the log sources. It’s likely that you’ll see one log type consistently taking up more space each day.

wss_logging sharepoint

For each of those tables that are taking up a lot of space, you will want to determine how much value you’re getting from them vs. the amount of data that they’re taking up.

Remember that you’re still going to have the ULS logs on your servers, so reducing the WSS_Logging database size won’t purge the log files off of your SharePoint farm servers. Those log files are saved or purged based on other settings independent of the settings that control what gets moved into WSS_Logging.

Sponsored

Change the Logging Settings for the WSS_Logging Database

So you’ve found your culprit, and you think by changing one of the log sources from the default of 14 days down to 3 that you can reduce the overall size of the WSS_Logging database. Congratulations!

Changing the log settings is easy enough to do, and it is done through PowerShell. So fire up a SharePoint 2013 Management Shell console and let’s get started

Get the Current Settings for Collating the Log Files into the WSS_Logging Database

Surprisingly, you won’t find “log” or “logging” in the cmdlet names that we’ll use in PowerShell for this. Those cmdlets are used for working with the ULS log files on the individual SharePoint servers. Since the WSS_Logging database is part of the SharePoint Usage and Health Monitoring Service, we’ll be using cmdlets that start with -SPUsage.

Find your usage log database settings

Or, more specifically:

Report only about usage logs that are enabled

wss_logging sharepoint Get-SPUsageDefinitionResults

Now that you see how many days of logs exist for each source, you can make your changes to meet your needs.

Change the Settings for the WSS_Logging Database

The easiest way to approach making the changes through PowerShell is to modify the Usage Definition directly through the Set-SPUsageDefinition cmdlet. However, you may want to perform the operation on several usage providers. If so, you can easily batch them together by saving the items that you want to modify into a variable. Then, use the Set-SPUsageDefinition cmdlet to change the items in the variable.

While there are several parameters that we can work with to customize the impact of the WSS_Logging database used by the Usage and Health Monitoring service, the main parameters we’ll use are -DaysRetained and -Enable.

Here are some examples:

Change the “Task Use” provider to be retained for seven days

Disable the “Task Use” provider completely

(Note: I’m not advocating the disabling or the reduction in log usage for the “Task Use” provider. I chose that one because it has a very short title so it fits better as an example.)

Set all providers to be retained for seven days

Find any providers that are enabled and set to 14 days retention, and set them to 7 days retention

You now have total mastery over your WSS_Logging database

Or rather, you at least have a better understanding of what it is and the role that it plays in the bigger picture of SharePoint logging and the Usage and Health Monitoring service.

If you have something to ask or share, let’s start a conversation in the comments below. And if you haven’t yet subscribed to the Petri IT Knowledgebase newsletter, you should do that right now – it’s free and helps keep you in the know with great tips and news from the IT world!

Sponsored

Tagged with ,