Monday, August 5, 2013

SharePoint - How to shrink "UsageAndHealthDB" database

Two months ago, our SharePoint farm got some "Usage Logging" issue (thanks for a windows update pack). After resolving the issue, a huge database "SP_UsageAndHealthDB" was left there.

In the database, we can see the space is consumed by "ULSTraceLog_Partition" tables.

So I went to Central Admin -> Monitoring -> Configure usage and health data collection, changed the "Maximum log file size" to 1GB.  I thought it would affect both file system and logging database, but I was wrong. The logging database was not affected at all. Two months later, all historical log data is still there.

I just cannot find any solution online. So, I have to remove the data manually.

select MAX([LogTime]), MIN([LogTime]),COUNT(1)
 FROM [SP_UsageAndHealthDB].[dbo].[ULSTraceLog_Partition3]

truncate table [SP_UsageAndHealthDB].[dbo].[ULSTraceLog_Partition3]

So far so good.

I totally agree that we should not modify any data directly from SQL Server. But, it seems that we don't have any other choice.

Please let me know if you can remove the log data from SharePoint platform, that will be strongly appreciated.

No comments:

Post a Comment