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:
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.
We can do better. 
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:
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:
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:
Then
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:
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