Reblog from Todd Klindt : http://www.toddklindt.com/ShrinkWSSLogging
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
Comments are closed