Shrinking the WSS_Logging database

The SharePoint 2010 logging database is a great tool for all SharePoint Administrators. Not only does it aggregate and consolidate a wide array of information across your Farm, it is also the ONLY SP SQL database which you can quite happily interrogate for your analytical and troubleshooting needs.

Details behind the WSS_Logging database can be viewed within the ‘Service Applications’ section of Central Admin, although once generated (by default), the only way to recreate this is via Powershell:

 

Set-SPUsageApplication –Identity “WSS_UsageApplication” –DatabaseServer “mysqlserver” -DatabaseName “WSS_logging_database”

 

Over time, the logging database can grow considerably and can cause storage concerns for your SQL DBA. You can however, put measures in place to purge unwanted data. The first option you have is reduce the data retention policy, which is set to 14 days by default.

Get-SPUsageDefinition


If for example, you wanted to reduce the data retention to three days, you can run the following Powershell:

 

$defs = Get-SPUsageDefinition

Foreach($def in $defs)

{

Set-SPUsageDefinition –Identity $def.Name –DaysRetained 3

}

 


Now finally, open Central Admin and choose, Monitoring > Configure usage and health data collection > Log Collection Schedule>

Execute the two Timer jobs:

The SQL logging database will now contain some free space which you SQL DBA can ‘free-up’ within SQL Management Studio or running the ‘DBCC ShrinkFile’ T-SQL command.

Advertisements

4 thoughts on “Shrinking the WSS_Logging database

  1. Good article. However, tried this on our WSS_Logging database and it stubornly refuses to shrink. It was running at 33MB and after moving to another SQL server it’s now 2.9GB and growing….

    • Hi John,
      Thanks for the comment – did you run the follow-up DBCC Shrinkfile T-SQL command? http://msdn.microsoft.com/en-us/library/ms190488.aspx
      You won’t see any visible reduction to the size of the database until this ‘free space’ is released by SQL.
      If you have no requirement to maintain the data of the wss_logging db, there is absolutely no reason why you can’t delete your exisitng database and start again…..

  2. This made clear for me what the -Identity parameter needed to be. Other sources I checked had generic syntax, but no hints as to real world use. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s