Sign up ×
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's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 7 down vote accepted

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.

share|improve this answer

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:

  • make sure you size the data files with some free space
  • have auto-growth set appropriately
  • have instant file initialization enabled if you can
  • monitor file sizes and usage
  • alert on auto-grows

Importance of data file size management

share|improve this answer

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.