Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Everyone is warning against shrinking a database, e.g. SQLAuthority
But will it be a valid option in my case? The database was originally more than 1 GB, but with a table removed from it (moved to another database), the database is now 100 MB when I do a backup of it, as opposed to the MDF being more than 1 GB still.
What if I shrink the DB, then rebuild all the indexes? Is all of this still taboo?
A 1 GB database is next to nothing as far as storage is concerned. If you are really looking to save space at that level, I'd be apt to question the environment specifics. Even on a small box, 1 GB is negligible.
I recommend you read up on the pit falls of shrinking your database files (more specifically your data file[s]). Paul Randal wrote a great article on this very topic. The moral here is to know what is happening and what you are doing to your data. If that is acceptable, and the business demands the smaller database then proceed cautiously. I will quote Paul Randal with his recommended practice in a situation like this:
So what if you do need to run a shrink? For instance, if you’ve deleted a large proportion of a very large database and the database isn’t likely to grow, or you need to empty a file before removing it?
The method I like to recommend is as follows:
- Create a new filegroup
- Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
- Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)Basically you need to provision some more space before you can shrink the old files, but it’s a much cleaner mechanism.
Will your database never need to grow to this size again? This is the main question that should be on the forefront of your mind when you consider shrinking a database. It's all about proper sizing, not just for today, but for the long-term foreseeable future.
And, most importantly, do not make this part of routine maintenance. For reasons why, see Paul's blog post.
Yes, shrinking the database will reduce the database size. And Yes, rebuilding indexes will increase the database size again, so you'll be back to square one
I've found the best collection of "why not shrink" articles here: Stop Shrinking Your Database Files. Seriously. Now.
Whatever you do - don't shrink your database. Have your disk cleaned up instead, to free enough space for your database, look at Paul Randal's recommendations:
I have seen cases like this and also ones where shrink does nothing at all (even though the database is completely empty). However, from personal experience they are few compared to the number of times it has worked as expected. If you are worried about it take a copy of the database, put it in a testing environment and try it out there first. That is the only way to really know for sure.
