Building SQL ConstantCare®: What Database Growths Do You Care About?

SQL ConstantCareSQL ConstantCare® alerts you when your databases have unusual growths.

No, not like that one on your neck. And you should really get that checked out. No, I’m talking about a database that suddenly jumps in size, making you suspicious about whether somebody created a backup table or built a set of ridiculously large indexes.

But Richie was working on an update to the unit tests, and we started wondering:

How big of growths do you personally care about?

If you were going to write the rule that alerted you when your database jumped in size, what would be your own personal size threshold? Does it change based on the database’s size? (Think about 1GB, 10GB, 100GB, 1TB, and 10TB databases.)

Let us know in the comments! We’re curious.

Previous Post
Updated First Responder Kit and Consultant Toolkit for October 2020
Next Post
Using Triggers to Replace Scalar UDFs on Computed Columns

20 Comments. Leave new

  • Because I try to set file growth settings relevant to each database, I care about multiple growth events in a short period of time. When a DB grows once a month it’s no biggie but when tempdb has 30 growth events within 10 minutes I know I need to see what’s going on there.

    Reply
  • As an ISV, my thinking might be a little different here, but I’m having a hard time thinking of any problems I’ve run into over the year that would have been noticed by a single jump in database size. Most of the problems I’ve run into tend to be changes in a rate of growth. For example, a database that’s generally been steady around 200GB all of the sudden starting to grow by 1GB per day. You don’t notice it at first because it’s such a small percentage, but if it keeps going for a few months, pretty soon your 200GB db is 300GB. Most of these aren’t really a problem either. They happen because of changes in software, changes in the business, or both (i.e, you do a marketing campaign and sign up a bunch more customers, your database tracking them will grow). But, sometimes, it’s because someone had done something horribly wrong.

    Reply
  • Matthew Cornish
    October 16, 2020 9:10 am

    Would a percentage of the overall size be best? 10%?

    1gb – over 100mb
    1tb – over 100gb

    Hmmm maybe not

    Reply
  • Just a thought, but maybe look through the msdb backup tables to get an idea of how large the database is on a day to day to basis then alert if the database grows outside of one std deviation of the average daily increase.
    We do something similar in my shop, but we actually have a process that logs the size of every db file day to day for a running 28 days and alert based on those growth rates.

    Reply
    • Great idea I wrote something like this a few months ago and am working on expanding it to our other servers.

      Reply
  • I am alerted if any important database is growing more than 1% in a single day. It is a high threshold (normal is 6 times less), but I am the last level of escalation and people before me have lower thresholds. Gigabyte number does not work for me because it is all relative to the database size, a 20 GB database and a 1.5 TB database have very different “normal” growth size in GB per day, they have very similar growth percent per day.

    In my previous job we had many cases where we had to delete the data after 3 years. For new apps the growth was predictable (and constant) after 3 months and almost zero after 3 years as new data was compensated by old data that was expiring. That makes it simpler and more complicated at the same time, in that case the GB growth was almost constant per database until 3 years and then again almost constant (zero), but with a different number.

    Point is: everyone has different needs, I would group it into 2 categories, by percent and by GB and let everyone choose a value.

    Reply
  • For me, the rule should be based on the baseline for growth, as it varies significantly depending on the database and its workload. I tend to baseline the growth by period and alert on significant % variances.

    So if my ‘typical’ (baseline) weekly growth is 10 GB and the DB grows by 30 GB that week, I’d want to know about it. If my typical monthly growth is 100GB and the DB grows by 300GB that month….you get the idea.

    I’ll project the monthly figures for future capacity planning (i.e. disk space). The weekly figures are more useful for detecting when someone has just created a table called ‘test’ and dumped 10,000,000 rows in it.

    Of course, I’ll monitor disk space constantly and alert if it’s low. What counts as ‘Low’ is fairly subjective – in an ideal world it would be based on projected database growth.

    Reply
  • When programmin a data warehouse for a well known streaming content provider, I was asked to program automated detection and responses to “unusual changes in data”. I programmed statistical analysis of the historical data into the processing, to identify standard deviation. If any change exceeded two standard deviations (or maybe it was three, can’t remember) the automation would send alerts.

    This is obviously a much more intelligent approach than choosing some arbitrary number, since the standard deviation calculation always considered all previous historical data, so adjusts intelligently and usefully to changes over time in the data.

    Reply
    • Just as an FYI the solution I implemented also detected unusual changes in the distribution of the data, not only data growth. This was especially useful in detecting inaccurate data in the upstream data feeds (which rendered automated reports to senior management to sometimes be ridiculous). This inaccurate data occurred most often when those feeds experienced a regression due to code changes in the device firmware which was calling home and sending us data nightly or in the upstream data capture prior to DW load.

      Reply
  • Yassine Elouati
    October 16, 2020 9:59 am

    I am interested in outliers when I alert on large + abnormal database growths. I used a method similar to the one Ken Ambrose is proposing where I would either alert on 3 standard deviations or a 95% or 99% percentile. However, I discovered it is prone to false positives, especially when you manage many servers and you do not have a very large history of growths.
    Then, my experience with a set percentage of growth did not yield good results because a small database will grow at a much higher rate than a multi-terabyte database.
    At this time, I use a logarithmic scale for growth rates based on the size of the total data file size of the database excluding the log file. The log file is monitored differently.

    Reply
  • If we were talking about a 1TB OLTP Sales database that has X number of INSERT transactions per day and that usually means ~1GB of growth per day, I’d probably say I’d be intrigued by maybe 1.5GB (to pick a number out of the air) However, that growth may tie in, for example with a sale the company is running which has caused a higher number of transactions (but as a DBA for that company you’d probably be aware of this)

    The Data Warehouse that loads the sales data would maybe have 3GB growth that day vs the normal 1.5GB but again, we would be able to account for it.

    I appreciate this is probably vague rambling but in a nutshell I’d say its a classic case of “it depends” and I’d say it depends on the “normal” growth rate, the workload type and knowledge of what is normal / expected for your environment

    Reply
  • For me, I know that it’s unusual for my databases to grow more than 1 go per day, except for a new client that we’ve just setup. So I have my own alert checking database size and alerting me if it grows more than 1 go a day. After that, in some circumstances it can be normal so I sometimes ignore it. But otherwise I will investigate and see why it’s growing faster than my other databases. (We have 13 production servers with more than 300 databases, going from a few go databases to 2.7 terabytes as of my biggest database today.)

    Reply
  • Michael J Swart
    October 16, 2020 10:37 am

    No alerts under 50GB.
    From 50 to 200GB, anything that is at least twice as much as the previous day.
    Above 200GB, Any growth of 100GB or more

    (Those would be my own preferences and maybe not applicable for others’ use cases)

    Reply
  • The growth that gets me too close to a full disk (e.g. growth steps available 1)

    Also negative growth – who’s shrinking my files???????

    Reply
    • Also any growth in a collection period that is greater than the default growth size. To me that’s a sign that either the default growth size is wrong (someone left the 1mb default in there) or a large amount of data turned up that might be unexpected.

      I shouldn’t have to follow up on that alert (no “We’re not making progress” emails).

      Reply
  • Wolf-Günter Hebel
    October 17, 2020 2:18 am

    In my opinion, it is would be more reasonable to detect ‘abnormal behavior’. Let’s say you database has grown 1 GB per day for the last n days, than that could be considered to be normal, and an alert should maybe be thrown if it has suddenly grown by 2 GB. But this would also be true if the ‘normal’ daily growth is 100 GB.
    What you need is a kind of baseline to figure out what can be considered to be ‘normal’. Then define a tolerance range (I would go on a percentage) and if the growths is suddenly outside of the tolerance, I would throw an alert. That way, the system would kind of auto-adapt if growth intentionally increases over time. And I would include the actual figures in the alert, so the admin can decide instantaniously whether it can be ignored or not.

    Reply
    • see my experience above using standard deviation. It does exactly what you are describing, using well proven statistical method.

      Reply
  • I think this should not be about when I have a 10 GB database I would like to be alerted if it grows by 10%. Also it should not be about I have a 100 GB database and I need to see if it grows every day one more GB. This can be monitored with your default monitoring solution.

    This should be more about the schocking events. Where a database grows about 50% or 100% in one day so you would immediately grap your phone and ask the application guy what they are doing.

    I would like to be noticed when a database grows about 50% within one day. This will give me some false positiv but also this false positive will help me to plan ahead with storage.

    Reply
  • Instead of size of the databases, I break it down by type. Type meaning sustem, tempdb, log and user data. For system databases basically any growth More than a few mb I want notice. Tempdb 1/2gb or two growths. Log files 5-10 fb is my tolerance. User database I break into user data and service databases (SSRS and staging databases) user database now we are talking

    User databases I think of it as number of growths per week. I have been working on pre growing the databases during idle times. Small databases I try for one per week. Medium databases twice and large nightly. But if the growths go wild, I want to know.. User databases I track and can predict growths. The industry I working mostly 7am-5pm to I have windows like this to work with.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}