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:

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