Skip Ribbon Commands
Skip to main content

Quick Launch

Todd Klindt's home page > Todd Klindt's SharePoint Admin Blog > Posts > How to tame your WSS_Logging database size on a test SharePoint 2013 server
March 26
How to tame your WSS_Logging database size on a test SharePoint 2013 server

First, let me be clear, do NOT do this on a production server. Repeat after me, “I will NOT do this on a production server, or Todd will rip all the tags off of my mattresses.”

Okay, now that we have that out of the way, let’s get down to it. A couple of times in the last week I’ve seen a question about culling a huge WSS_Logging database in a test VM environment. When you start considering snapshots and thin provisioned drives, a 3 GB WSS_Logging DB can be a real bummer, with almost no benefit to most people. The WSS_Logging DB is created with the Usage and Health Analysis Service Application and logs all kinds of usage information. Unless you’re testing that directly, it probably doesn’t do much good. However, having it around is probably good, in case anything you are testing uses it. I suggest a happy medium; leave it enabled, but reduce its overhead. That’s what this blog post will tell you how to do.

By default the WSS_Logging keeps 14 days’ worth of information. That results in a big database. 3 days’ worth of logging is probably sufficient for most test VMs. Here’s how mine looked this morning:

Logging 3-26-2013 10-52-30 AM

The WSS_Logging DB’s MDF and LDF are taking 2.2 GB with 14% of the MDF free and 60% of the transaction log free. Since the transaction log is 6 MB, I didn’t care about that. Since this is a test VM, I shrunk the DB, to see what that would get me. I got the 14% back. That shrunk it down to 1.7 GB.

 

Logging 3-26-2013 2-15-29 PM

We can do better. Smile The next thing I did was change the Usage retention from 14 days to 3 days. I do that with PowerShell. Here’s how I did it:

 

Logging 3-26-2013 2-49-14 PM

Get-SPUsageDefinition shows me all the things that are retained, and for how long. I want to set them all to 3 days from 14. I use the following PowerShell to do that:

Get-SPUsageDefinition  | ForEach-Object { Set-SPUsageDefinition $_ –DaysRetained 3}

The next time the Usage Log File timer jobs run it’ll clean out everything more than 3 days old. If we want to manually trigger those jobs we can use this PowerShell:

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

The second Timer Job failed because I haven’t enabled it on my farm. You may or may not get that same error.

Now let’s check in with SQL Server Management Studio and see how our database looks:

 

Logging 3-26-2013 2-34-56 PM

The MDF file is still 1.7 GB, but it’s got a lot of unallocated space. We can shrink the database to get that back:

Logging 3-26-2013 2-35-39 PM

Then

Logging 3-26-2013 2-36-18 PM

 

Normally shrinking a database is the Devil’s work, but since this is a test VM, and since we don’t anticipate the database growing it’s less demonic.

Once that’s all finished we can see our database is taking about 300 MB on disk:

 

Logging 3-26-2013 2-46-29 PM

That’s much better.

Again, you only want to do this in a test environment. Don’t do it in production.

tk

ShortURL: http://www.toddklindt.com/ShrinkWSSLogging

Comments

Re: How to tame your WSS_Logging database size on a test SharePoint 2013 server

Such a simple concept, but one I hadn't considered. I'll have to do this for my test environment - promise - not production. Thanks!
 on 3/27/2013 6:49 AM

SQL report

How did you generate the Disk usage report, i'm not greatly familiar with SQL Managment studio.
 on 4/10/2013 8:11 AM

Yeah, mine won't shrink

I've gone through this several times, and my WSS_Logging db is growing fast with little to no use on the test server.

It's up to almost 5gb, and won't shrink.  I've set it for 3 days retention, but it still grows.  I finally had to turn off collection.
 on 5/30/2013 1:29 PM

I did it on a production server

i had to, the database is taking so much space, and i found this very helpful, although before hand  i did a full backup just in case.
but i did it on a production Sharepoint
 on 7/15/2013 10:05 AM

Thanks!!

Cleared up 2 GB of data. Thanks a ton!!!
 on 11/8/2013 3:16 AM

Re: Thanks!!

Awesome! I'm glad it helped. When I run for President can I count on your vote?

tk
Todd O. KlindtNo presence information on 11/8/2013 8:24 AM

Production Server

So where can we learn how to manage a usage database in production? I have been unable to stem the growth of the usage database.
 on 3/4/2014 9:38 AM

Dev Server

Does this take awhile for the database to be shrunk?  It seems like mine has been going for a long time.

Thanks
Kim

 on 3/18/2014 9:05 AM

Re: How to tame your WSS_Logging database size on a test SharePoint 2013 server

Why not doing this on production environment ?
 on 3/20/2014 10:13 AM

Re: Dev Server

Kim,
It depends on how fast your server is, how big the database is, etc. The time is usually measured in minutes, not hours, days, or fortnights.

tk
Todd O. KlindtNo presence information on 3/20/2014 10:35 AM
1 - 10Next

Add Comment

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.

Title


Body *


Today's date *

Select a date from the calendar.
Please enter today's date so I know you are a real person

Twitter


Want a message when I reply to your comment? Put your Twitter handle here.

Attachments

 

 Please Support my Sponsors