Reduce the WSS_logging SharePoint 2010

This post is going to provide a quick overview of the timer job “Diagnostic Data Provider :Trace log” which is being used rarely by users.

Background

I recently came into a very interesting case where the wss_logging database has reached the 260 GB .

By generating the report Disk Usage by Top Tables report in , we found that the top tables are the ones with the naming convention dbo.ULSTraceLog_Partition(x) where x = 0…31

So The next logical step was to check what kind of data these tables use and what i found that it is full with very old data from ULS logs files .

So how this data is supposed to be removed to reduce the size of the wss_logging.

Troubleshooting

By digging a bit around these tables , I found that these tables don’t exist by default the wss_logging database. They got created once you enable a timer job called “Diagnostic Data Provider :Trace log”. This timer job is disabled by default.

In my case after this timer job was enabled for a while and then it got disabled back the data remained in the partitions. And in order to get rid of these data we had to enable the timer job “Diagnostic Data Provider :Trace log” and also to execute the following script where I set the retention for the logs to 1 day and run the job

$job=Get-SPDiagnosticsProvider | where {$_.Name -match "job-diagnostics-uls-provider"}

$job.Retention = 1

$job.RunNow()

You may need to shrink the database afterwards to release the unused space.

Conclusion

Diagnostic Data Provider :Trace log is a timer job ,which is disabled by default, responsible for importing the uls logs from system files into the logging database .

Enabling this timer job is going to create 31 tables in the logging database “WSS_logging” with the following naming convention dbo.ULSTraceLog_Partition(x) where x starts from 0 to 31

 

Each partition is going to save the uls logs of specific date for example

dbo.ULSTraceLog_Partition28 is going to save the uls logs for the 13th of the month and dbo.ULSTraceLog_Partition29 is going to save the uls logs for the 14th of the month … so on

Disabling the timer job Diagnostic Data Provider :Trace log is not going to remove these data.

In order to remove the old data either to wait the month to be finished so it can be refilled with new data or you can execute the following cmdlet

$job=Get-SPDiagnosticsProvider | where {$_.Name -match "job-diagnostics-uls-provider"}

$job.Retention = x

$job.RunNow()

Where x can be any value from 1 to 14