Note: This is a repost of an older blog by Sean that’s still applicable. We’ve updated it with a note or two on how Minion Backup – our free backup solution – and Minion Enterprise – our management solution – can help.
I just got this question in the user group and thought I’d write a blog instead of just answering a sub-set of users who could benefit from it. The question was:
I have customized the values of the Auto growth according to the size of the database and the rate at which it grows. I have noticed that Auto growth kicks in about every 3 months – 6 months on an average. Is that OK? I have read articles where the advice on it ranges from “Auto growth is OK” to “Auto growth should kick in only during emergency”.
This is one of those topics that comes up again and again, unlike AutoShrink which I hope is settled by now. I suspect it keeps coming up because there’s no real solid answer.
Ok, so whether or not to AutoGrow your files. I’m going to talk about both data and log files together unless there’s a difference. So unless I call one out over the other, I’m talking about them both.
Yes. And, no.
You should definitely use AutoGrow. And you should definitely NOT use AutoGrow. That’s my way of getting around saying “it depends”.
It depends on a few factors really.
- What you’re going to do with the files.
- How big your environment is.
- How many other files are on the drive.
- How much activity is on the files.
- Monitoring method
Maybe there’s more, but that’s all I can think of right this second, but you get the idea. Ok, so let’s go through them one at a time.
1. What you’re going to do with the files.
From time to time I step into a shop where the DBAs have bought into this idea that AutoGrowth is bad so they have some job setup to monitor the size and they grow the files manually. Now while that sounds like a good idea, it can cause more problems than it solves. Let’s look at a scenario I’ve encountered more times than I care to try to count. You get a request from a group to restore the DB to a dev or maybe a QA box so they can do some testing. This is a common scenario, right? I’ve done it plenty in almost every shop I’ve been in.
So you go to do the restore and it fails telling you that there’s not enough space on the drive. You look and there’s not that much data in it so it should fit on the drive right? No, not right. The drive has to support the size of the file, not the size of the data. So if you’ve got a 50GB drive, and a 100GB file it will fail even if there’s only 20GB of data in that file. So now what do you do? Do you go beg the SAN guys for more space or do you manage your files in smaller increments?
With AutoGrow you can set your own growth rate, so you can have it grow the files at whatever interval you want. So how is manually growing the file going to add anything to the equation here? And with Instant File Initialization (IFI) you don’t even have to worry about incurring the cost of zeroing out the file unless it’s a log.
Now, for log files specifically, I know some of the top experts say that you can have perf problems if you grow your files too much and get too many VLFs, but honestly that problem really doesn’t come up that often. And logs are very volatile. Lots of things log activity that you don’t realize and I wouldn’t want the log file to rely on me. And again, I can’t stress too much that it really matters what you’re going to be doing with the files. If you’ve got an extra 60GB of space in your log because you’re afraid of VLFs, then you’ll need that extra 60GB on every other system you plan to restore the DB on. And you may not be afraid of the VLFs on those lower-level servers.
Minion Backup logs VLFs before every log backup, so you can track how many there are. This can help you see if you’re growing at the correct rate. And, you can shrink the log to the size you want to help correct any VLF issues that may occur. Even better: MB lets you shrink them only if they’re over a certain size.
2. How big your environment is
Now let’s talk about large enterprise environments. I tend to be in really large shops with hundreds or thousands of servers. And I don’t know about you, but I don’t wanna spend my time managing file growths. Consider my last environment where I had over 900 servers with over 4,000 DBs spread across all of them. And that was just prod. I’m not going to do that kind of analysis on all of those servers and manually grow all of those files. And it’s honestly just ridiculous to even try. There are 2 ways I could solve a problem like this.
I could develop a process where I monitor the free space in all the files, and when it reaches a threshold it grows the file by a certain amount. Hell, that’s just a homegrown version of autogrow isn’t it? So that’s not a solution really.
I could also use autogrow on some of my boxes and manually grow my really important or trouble boxes. And again we’re back to “it depends” aren’t we? What we’re saying here is it’s ok to use autogrow on some servers and not on others, which means there’s no solid answer. You just can’t spend all your time growing files. Use autogrow here unless you have a reason not to.
3. How many other files are on the drive?
This argument may or may not have any teeth… it just depends on how you look at it. The main reason for manually growing your files on drives where you’ve got a lot of other files is for the fragmentation. And here I’m talking about fragmentation at the filesystem level, not inside the files themselves. If you’ve got you files on a drive with lots of other files and they’re all growing, then they’ll be growing over each other esp if they’re growing in smaller increments. So you could fragment your drive pretty easily and that can definitely cause perf issues. So the solution is typically to manually grow the files to a larger size so it reduces the amount of fragmentation you create when they do grow. And that does have merit, but why not just set the AutoGrow setting higher then?
I can see a reason why you wouldn’t. If there are a lot of DBs sharing that drive and they all grow fairly often, then you wouldn’t want to AutoGrow it to a certain size and have it fill up too much of the drive and starve the other DBs. The most logical way around this issue though is twofold:
AutoGrow at smaller increments. Unfortunately, this may put you back in the fragmentation scenario though. If you go this route then you need to defrag the drive on a regular basis and you should be ok.
Split those DBs off onto their own drives. This is the best solution because you get stuff for free. Things like simplified space mgmt., 0% fragmentation, and I/O isolation are all things that come along for the ride when you put DB files off onto their own drives.
Minion Enterprise allows you to see and configure your file growth rates across dozens or hundreds of servers, centrally. As long as you’re there, you can also see exactly where each database file resides on each server.
However, all that said, if you can’t put the files on their own drives and you’re really afraid of starving the other DB files, then your only real choice may be to monitor the size and grow manually. But this shouldn’t be the norm if you’re in a big shop. Keep this kind of activity to a minimum if you can help it.
4. How much activity is on the files.
This one is almost like the other one, only this doesn’t necessarily rely on what else is on the drive. This counts even if the file is on its own drive. If the file grows a lot every day or every week, then you don’t want to take a chance on missing an email alert or whatever else you use and having the file fill up because you didn’t grow it. So while there may be some exceptions, my skills are better spent elsewhere than growing files manually.
5. Monitoring method
Many shops monitor with 3rd party tools and those tools monitor disk space. However, none of them are smart enough to know the difference between a full drive and a full file. You could have a 100GB file with a 99GB data file on it and the alarm will trip even if the file is only 3% full. And depending on whether or not your monitoring team is friendly, they may or may not help you out by either turning off the alarm on that drive, or doing something so that it knows something about the space in the file. I’ve honestly worked with both friendly and unfriendly teams. So I could either setup an outlook rule to ignore all space alerts (bad idea) or shrink my file back again so it didn’t trip the alarm.
Minion Enterprise is a management solution – not technically a monitoring solution – but nevertheless, it collects data on drive space and file utilization. And, it comes with configurable drive space alerts.
So you can see there are several factors involved with this decision and chances are you’ll have a mixed solution. I’ve worked in shops where I never managed space at the file level, and shops where it was very necessary, and everything in between. For me #1 above is one of the biggest deciding factors. I’m constantly fighting DBAs growing files a lot to be proactive and then we can’t restore to any of the other environments. Even DR becomes an issue because you have to have that space anywhere you restore those DBs. And that’s a lot of extra space to keep on hand for such little return. Don’t get me wrong, I’m not a big fan of thin provisioning either. I think that’s going a bit far, but it’s basically the same thing at the SAN level. This provisioning is AutoGrow for the LUN itself. And the biggest problem I have with it is that they tend to not grow it enough or they set the threshold too high so the file fills up and brings the DB down while you’re still waiting for the LUN to expand. If they can get it right though it’s not the evil it used to be. So what we’re really doing with AutoGrow is we’re thin provisioning our DB files. And that’s actually much easier with IFI because they expand in just a couple seconds. That’s only for data files though. Log files still have to be zeroed out so you can run into the issue now and then where the log file is still growing when the process runs up against the end of the current file and everything stops. Hey it happens. Those are the cases where you might consider manually growing your log files. These would be more DSS type systems where it’s unlikely that you’ll restore it to a different box.
Having huge files can also slow down your DR plan. If you’ve got a huge log file and a 30min SLA, you could easily spend more time than that zeroing out your log file. So you’ve orchestrated that you’ll miss your SLA just by trying to make sure you don’t run into an almost non-existent VLF issue. So you’ve got to consider that too.
So anyway, I hope this helps you at least consider the different factors involved in making this decision. Leave me comments if I’ve messed something up really badly. Or if I’ve gotten something really right. Hell, just tell me I have great hair and call it a day.