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/

| improve this answer | |
  • 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

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.