I want to reduce the size of the file from this db WSS_UsageApplication in my sql server/sharepoint farm. How to reduce the this huge file size.
WSS logging database grows quickly, it cause space issue as you are seeing. you have to tweak some setting to reduce the size.
- By default System retains 14 days of usage data. You can check the
current settings by running the following command:
- If you check the database from SQL server, you will notice that RequestUsage table is taking most of the space.
- So now you have option to Set the retention period from 14 days to less (maybe 10 or 7 days, spending upon the space these things is consuming.) You can use the Set-SPUsageDefinition to restrict it.
- Once you do it, you have to manually run the two timer jobs ‘Microsoft SharePoint Foundation Usage Data Import’ and ‘Microsoft SharePoint Foundation Usage Data Processing’.
Read this blog for more information. https://blogs.msdn.microsoft.com/manhar/2012/04/17/how-to-reduce-the-size-of-logging-database-or-how-to-purge-the-old-data-from-logging-database/
I have been having this same problem and today I found some interesting methods to try and get to the root of the problem. My concern is that reducing the logging period is a band-aid solution and if someone is doing something incorrectly on SharePoint then the problem might continue to get worse.
First I found a SQL query that helps you find large tables within the database. I ran this query on my WSS_UsageApplication database.
select top 10 schema_name(tab.schema_id) + '.' + tab.name as [table], cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb, cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb from sys.tables tab join sys.indexes ind on tab.object_id = ind.object_id join sys.partitions part on ind.object_id = part.object_id and ind.index_id = part.index_id join sys.allocation_units spc on part.partition_id = spc.container_id group by schema_name(tab.schema_id) + '.' + tab.name order by sum(spc.used_pages) desc;
Thank you to this site for that query! Find Large Tables
From there I was able to find that TaskUsage had one table with over 30GB of data in it. Then I found this article on Microsoft for SharePoint 2016. The reporting columns look the same as the 2013 environment I was working in.
I thought it was discouraged to query SharePoint data in the database, but this post from Microsoft seems to show that it is allowed. Unfortunately I haven't found my issue yet, but I think I'm on the right track with these