1

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.

enter image description here

1

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:Get-SPUsageDefinition
  • 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/

6
  • What I did now is, I removed/dropped this DB after login to SQL server machine thru SSMS. So now, I got those space in my drive. – userAZLogicApps Feb 28 '17 at 15:48
  • 2
    But If you did not unprovision the Usage service application then you will get the errors all over the sharepoint (event log, uls logs etc) – Waqas Sarwar MVP Feb 28 '17 at 15:49
  • So, retention using the set-spusage definition is the only way to tackle this issue? What will happen , if the production SQL server machine has just 100gb in its drive & this issue consuming 90gb(say) on a day then the sp site wont work seemlessly.Moreover, just because of this issue my sp d 2013 workflows behaving strangely . failed to publish, unable to get the current list item etc etc... – userAZLogicApps Feb 28 '17 at 15:57
  • 1
    either dont use this application or reduce the retention period (may be 3 days). if space is really issue as you mentioned then i would reduce the retention to lowest level i can do.... – Waqas Sarwar MVP Feb 28 '17 at 15:59
  • 1
    I am not sure about other products as no exposure with them. MSFT taking DB issues seriously, but sometime they are lack of documentation or best practice to configure these kind of things. Another example if you enable auditing on a site collection and if you dont plan it...it will cause the space issue on db servers. i had 700GB audit table couple of years ago, ...as SP admin, we have to monitor the SP servers, SQL servers etc – Waqas Sarwar MVP Feb 28 '17 at 16:07
0

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.

View data in logging database

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.