SQL 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.
34 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.
Kris – gotcha, but that’s not what this post is about. This post is about the size of growth. (We only sample daily.) Thanks!
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.
Would a percentage of the overall size be best? 10%?
1gb – over 100mb
1tb – over 100gb
Hmmm maybe not
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.
Great idea I wrote something like this a few months ago and am working on expanding it to our other servers.
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.
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.
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.
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.
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.
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
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.)
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)
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???????
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).
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.
see my experience above using standard deviation. It does exactly what you are describing, using well proven statistical method.
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.
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.
Brent – do you work with MSP’s? Had some ideas but wondering if that is something you are interested in.
Curtis.
Yep!
What is the best way to chat with you about that?
Sure, click Contact Brent at the top of the screen. Thanks!
Why do SQL Constant Care report constant say that maintenance plans are outdated?
I have found nothing on your site that says this so far.
It has to do with *your* specific usage of maintenance plans. Classic example: folks who rebuild indexes then update statistics. Make sure to read the recommendation carefully, and then for questions, hit reply on the email. That way I can see your exact config.
Unfortunately, replying to the email will not work because it was auto-forwarded from our IT department.
The specific quote is this: The maintenance plan xxxxx has a step to shrink databases in it. Shrinking databases is as outdated as maintenance plans. (Schedule: [xxxxx])
I realize that shrinking is not recommended in many, or most, cases, and while I was under the impression that it was simply doing a truncate and not a more aggressive shrink, that does not negate the fact that the email says “as outdated as maintenance plans”, generally implying that maintenance plans are a thing of the past and should be replaced by something newer, but not specifying what.
I can forward the Constant Care email to your help account, if you think it would be useful.
Yep, definitely forward it – that way I can work with you on it. I can’t work with you publicly here – I run the risk of divulging information you’d rather keep private about the server. Hope that’s fair. Thanks!
No issues with that.
I just wasn’t sure if that was the right place to send it.
Sending it asap.
Note that I did forward it to your help@ email address.
I haven’t seen it yet. You may need to check with your IT team – sometimes the emails are filtered if they have seemingly private information.
I need to hit the brakes here though: your email isn’t one of the SQL ConstantCare subscribers. SQL ConstantCare is sold per email address. Go ahead and have the subscriber contact us, and we can go from there. Thanks!
Yes, that email would likely be our sxit@ email address as that is who it was forwarded from.
I specifically forwarded it to the email address shown over here: https://www.brentozar.com/contact/
Note that I am not asking for you to help diagnose the information in the SQL Constant Care email.
I simply wanted to know why that phrase was included and where the information was that supports that position.
I only forwarded the email for your reference so you could see where it is stated.
I am not trying to get any kind of technical support or circumvent any of your normal processes.
And I am totally down with doing that kind of discussion with our paid customers. I hope that’s fair. Thanks for understanding.
Ok, thanks for the clarity on that.