Ever notice that there are so many bad things that can happen to a DBA? One
of the many errors that will make the blood of DBA run cold is 'insufficient
space'. In this article I'd like to talk about some basic strategies both for
avoiding it as well as dealing with it if...or when, it happens.
At least in SQL 2000 the default is for both data and log files to grow by 10
percent when needed with no limit to the max size. Let's start by looking at how
you configure these in Enterprise Manager:
Whether you change these at all depends on both your environment and your
preferences. Leaving auto grow selected decreases the tedium of having to grow
the files manually - and to some extent of having to keep track which files are
about to need to be expanded. There are three down sides to leaving it set to
the default in my opinion. One is that it's possible to have it grow until it
consumes all available disk space. The second is that while 10 percent is
reasonable for smaller files, you don't always want to grow a 10g database by 1g
at a time. And finally, you do suffer a performance degradation when the file
grows - how much depends on how much you grow and how good your disks are. Even
with all those seemingly negative comments, for the most part I leave the
defaults in place, overriding the growth to a 100m or so at a time if the file
is over 1g.
It's important to note that whether you use the auto grow or not, set a max
file size or not, it's still possible to run out of space as far as SQL is
concerned. Running out of data or log space is equally bad.
There are a lot of gotcha's that are possible with regards to space, here are
some that are pretty common:
- You use the standard maintenance plan to rebuild indexes. Depending on
the options you select this can enlarge both db and log quite a bit.
Frequent log backups during the rebuild period are a good idea.
- Every time the database size grows by a meg, you need another meg free
to back it up. Say you've got a 100g drive with a 40g database and for
purposes of discussion the backup is also 40g, leaving you a cool 20g free.
Add 10% to the db driving the size up to 44g, you now only have 12g free
after backup - and that's not counting your log backups.
- Log backups fail. Could be because you're out of space to run the
backup, or the job hangs up, or other bad thing, the result is your log file
will grow and grow until you run a successful log backup.
- Old backups don't get deleted. This can happen if your maintenance plan
- Replication log reader fails, forcing growth of the log until the
transactions are read or the subscription expires.
- Replication distribution agent (or queue agent) fails, causing pending
data to accrue forcing growth of either the distribution db or the publisher
- Someone loads a large amount of data. Remember it's not just the data,
it's the increase in space used by indexes as well.
Nothing like having a good set of rules to live by, so here are
mine when it comes to preventing out of space errors.
Rule #1 - monitor the free space available! Monitoring
doesn't have to be sophisticated, though most server monitoring utilities
include this as a core component. Dirt cheap is to set a reminder to check disk
space once a day manually. You can also set an alert to warn you when a file
grows, realizing that just because the file grew doesn't mean you're in the
danger zone automatically.
Rule #2 - assume you will run out of space. I keep
several 1g files as placeholders on each drive that might run out of space. If I
absolutely need space I can delete them while I try to shrink a log or take
other action to try to reclaim some space. Here are some additional
Add a new filegroup to the database or log file. It's common
to have a lot of extra space on C: these days, usually only the OS installed
there. It won't be fast, but it will give you a chance to work the problem
without stopping work.
Look for any file you may be able to move somewhere else.
You might be able to detach a non critical db and move to another drive, or
you might have backups from today that while important, are less important
than getting users back to work.
This may never happen to you. Or it might happen tomorrow. Be
proactive in trying to prevent it, and be schooled in the options just in case
Are there other strategies you use? Horror stories about running
out of space? If there is enough interest I'll do a follow up to talk about
other interesting options that readers may use.