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.
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.