Database Total Size alerts all over the place

The other day, I logged into our SW SAM (6.3) instance, and found that about 30 SQLServer databases were alerting critically about their "Total Size" property.

These databases ranged in size from 6MB to 300GB, and there is about 400Gb of free space on the volume on which they are stored. All of them were green on their "Percent Growth in last 24 hours".

When I went in to view the thresholds for each database, I found that they are all different, based on different baselines. I investigated the latest baseline thresholds, recalculated the recommended thresholds, and applied them. In most cases this returned their status to green. In a few cases I did have to increase the recommended baseline threshold, and often received the message:

Which makes no sense to me, as there is plenty of free space, and because much larger databases were allowing much higher thresholds (by orders of magnitude).

So my questions are:

Why did this become a problem overnight?
What are the baseline thresholds actually based on
How is the max value for the threshold defined
and most importantly, how can I shut it up until there is actually a problem?

Any assistance would be greatly appreciated.

Find more posts tagged with

database storage

sqlserver

sam 6.3.0

Accepted answers

mesverrum

I would say that if you don't want it to turn the db object red then just erase the values from warning and critical on the template, no need to set it to an arbitrarily high value just blank it out. I find that in sam templates, especially the sql one, there are lots of metrics I may want to track for context info but I don't need warning/alerting thresholds for them. Another one that may be noisy for you is the index fragmentation. If your dba is on top of things then they would presumably have a scheduled job to fix index fragmentation when and where it matters and you don't need solarwinds constantly stressing out about them, so that's probably a good candidate for having the threshold removed.

All comments

orioncrack

FileGroup space in SQL is different than Volume space. You need to look at your SQL DB filegroup sizes.

Right click on a database in SSMS console and choose report, look at the DB Files space usage..

mesverrum

I'm confused about the use of a baseline for your total size thresholds. Are your databases not allowed/expected to grow? The baselines don't project growth trends very well, they only look at historical values over the previous week and make estimates about the typical ranges based on the observed values. So eventually whenever you the db does grow by enough it would trigger that alert again. Recalculating the baseline will adjust the windows of "normal" values a little bit and probably clear the alert status for a couple days but it just really isn't a good use case for baselines unless your db shouldnt ever be allowed to use more disk space.

Looking at your specific questions

Why did this become a problem overnight? - The databases grew beyond 2 standard deviations of the values that they had the week when the old baseline was set.
What are the baseline thresholds actually based on - Whatever the observed value for this metric was over the previous week when the "use baselines" button was clicked, with a range of 2 standard deviations for Warning and 3 for Critical. See this KB Thresholds
How is the max value for the threshold defined - see above ^
and most importantly, how can I shut it up until there is actually a problem? - Think this out logically, at what threshold would you ever need to be warned about the size of one of your databases? Unless there is a maximum size of a DB where your application starts to have performance issues there probably shouldn't be a threshold for this metric, it should be informational. If i wanted to get alerted about anything I would have some monitors pointed at the disk volume itself watching for free space, but not set up thresholds on the sizes of individual db's

jrohrer

Thanks for the link; the information on baseline calculation is illuminating.

I'm sure you can tell, I'm new on this platform, and some of your comments focus on the Why, rather than the how or what, which is great, but to be honest, we have just accepted defaults, and overridden in places where SAM claims something is wrong, but there appears to be no problem (like a 40GB database growing to 40.5GB). I don't believe we explicitly told SAM to apply dynamic baselines as the default for the AppInsights for SQL Database Total Size.

Based on your statements, I can infer why the issue arose: there was an issue with an account used to run our polling that caused most hosts to lose connectivity for about a week, so the thresholds were not recalculated in the most recent week. That explains a lot.

I may be using the wrong terminology, I do not mean "alerts" as in emails, but instead that it marks the database, server, and any application groups as being in a critical state, which is undesirable for a dashboard, as the monitored application is running as well as it had the prior week.

It sounds like my best bet is to modify the template to use a static value of 1TB, and remove any database specific overrides. I agree, the total Size catagory is not particularly useful to my org. What do you think? or should I just revert to the standard {$Use_Baseline} and expect this kind of problem in future after connection issues?

For bonus points, I didn't see any information about why 10,000,000 is an acceptable baseline threshold for one database but 9,999,999 is beyond maximum for another. perhaps not essential to my fix, but puzzling none-the-less. is it based on 3.1+ standard deviations from the mean?

mesverrum

The idea with the baselines is that it will determine the appropriate threshold on a per database basis, so yeah each one would get a different threshold based on the values it had before. Also keep in mind that one you hit the use baselines on a SAM template those values are only calculated that once and will not automatically update again until you clear the baseline and hit the button again.

mesverrum

jrohrer

Thats a great tip. I had no idea that you could just empty the box. I guess that explains why there isn't a simple UI for saying "I don't care about this one thing".

Thanks again for all your help. If you have any other thoughts on this, I would appreciate hearing them.