I have a SharePoint 2013 server whose SP_UsageAndHealth database started growing out of control this morning. It's grown from about 50GB to a terrabyte and a half in the space of a couple of hours. My research indicates this DB is a temporary repository for - as the name suggests - usage and health data, but how temporary? Can I shrink it? Can it be dropped or deleted or something and start from scratch? The TRANSACT SQL query that was obviously filling it was killed with no obvious ill effects but I need that space back.

  • Normally you wouldn't shrink an SQL database, but when it comes to shrinking a 1,500GB database back down to 50GB I think that's justified. Shrinking a database should not have any data integrity issues so it should be a perfectly safe option (just schedule an index rebuild after the shrink). – Mark Henderson May 7 '15 at 23:22
  • You may need to take a transaction log backup before you can shrink it, or if you can't do that, change the recovery model from FULL to SIMPLE, shrink the transaction log, and then back to FULL again. But you need to be aware of the implications of doing this, it's not something that you would normally do, and it's not something you should do lightly. – Mark Henderson May 7 '15 at 23:23
  • I gave dbcc shrinkdatabase a crack and it's actually full of pages. Any SharePoint guns able to tell me what this is full of and how long it'll live there? – Elomis May 8 '15 at 0:34

Ah got it. In SharePoint's central administration web app there's settings for how this gets populated in monitoring, as well as two jobs which tidy it on a schedule. They can be run now with a button, and then dbcc shrinkdatabase tidies it up.

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.