When you're faced with a database being too large, for whatever reason (growth is more than what you expected, you're running out of disk space, you run out of buffer pool space in memory) there's a few questions you probably want to ask yourself before taking action:
- What happened to cause the database to be too large?
- Where exactly am I seeing problems?
- What steps can I take now to alleviate my immediate space concern?
- What steps do I need to take to keep this from happening again in the future?
A lot of times, growth is to be expected if people are using the system that adds data to the database. This is, after all, the purpose of a database. However, understanding the root cause will be key in addressing the issue long-term, so doing that work up front will save time later. This may mean you need to take a look at use patterns using DMVs or other mechanisms.
The next thing you'll want to determine is where you're seeing the problem. If you've separated out your data from log drives you should be able to easily tell where you're seeing growth. If it's in the log file, figure out how you can better address the log file growth (more frequent log file backups?). If it's the database, your options become a bit more plentiful. If it's the Temp db, figure out what is causing temp db growth.
Once you determine what caused the data growth, you can take steps to determine how to immediately deal with the problem and address it long term. Deleting data out of a database can be a pretty drastic step if you are running with logging on (and you should be). For one thing, the log file will grow because it has to keep information about the deletes in there, until you're able to back up the log file. For another thing, as you noticed, SQL Server won't automatically adjust the size of the files when you remove data. From a long-term point of view, you can assume that if the database ended up a certain size through normal usage it will get there again... so spending time shrinking and growing the files would be wasteful.
To immediately address the issue there are a couple of things you can do:
- If you are absolutely sure the database won't just grow again, you can shrink your database. You won't want to do this because the database is probably going to grow again, and it's not a long term solution that helps address the actual problem.
- You can enable Page or Row compression to make the data smaller, if you aren't experiencing CPU pressure. The general trade off is that while Row and Page compression reduce size on disk (and therefore memory usage) it takes CPU cycles to compress. You also need to make sure that new objects added to the database are compressed.
- You can attempt to limit the amount of data being entered into the database.
By not only pinpointing the cause but determining a course of action for the long term, you'll be better prepared for the future and may even save yourself a panic.