How to keep the size of the MDF files under 500GB

Question

Are there any suggestions to keep the size of the MDF files under 500GB, except the steps mentioned in the below KB article?

Optimize the database used by SolarWinds Platform products

mesverrum · Answer

The size of the database has a few factors, some of which you can control pretty easily and others are more complicated to manage.

* Number of objects being monitored - You probably need to monitor all your nodes, but when it comes to interfaces and volumes there is usually room to do some trimming here and remove anything that doesn't provide significant value.  If you use SAM the AppInsight templates can create a lot of noise so I generally like to make sure a customer is really getting full value from them and if not we can use more simplified templates to reduce the component count (and therefore the size of the DB tables) substantially.
* Retention settings - The default setting for most of the application is 7 days at full detail, 30 days at hourly averages, and 365 days of daily averages.  If you have modified these settings then that can have a big impact on the size of your DB.  If you are in a situation where the DB size is becoming problematic you may just need to reduce retention.
* Polling intervals - Most objects have default polling intervals from 2-15 minutes.  Increasing the frequency of polls very easily increases the size of the tables used to store all that polled data until it hits the roll up periods mentioned above.
* Which features are in use - Orion has a lot of modules and features, some of them generate a lot of data on disk but may not be useful to your team.  You'll want to validate that all the features you use are actually worth it, for example I once worked with an environment where thousands of linux machines had custom UNDP pollers assigned to collect a variety of disk performance stats. But none of the details views actually had this data showing in any widgets, there were no alerts using this data, and when I asked the support teams who got tickets for those systems they didn't know Orion collected this data and they didn't feel like it would be super important on their end if I just turned it off.  Removing that set of UNDP's by themselves freed up 100 GB in my environment.  Over the course of two years I was able to work with our sysadmins and NOC teams to optimize the data we were collecting to the point where we went from having a DB size over 800 GB to where it was less than 300.  The end users felt like the whole system was more useful than when we started despite the fact that I turned off a lot of low value features. We strongly moved away from just collecting all the data just because we could to being focused on data that was determined to be actionable. 
* Use SSMS to run the Tables by size report and understand which tables are taking up the most room on disk, and then decide if that data is actually valuable enough to justify collecting and storing or not.  Everything we poll has a cost to collect, and a cost to store, and a cost to display in the GUI.  Some data does not provide enough value to justify those costs.

donrobert5 · Answer

SQL Server configuration best practices for SolarWinds Platform products