Take the 2-minute tour ×
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
5  
1GB? You mean, like 1/32th of a thumb drive? Really, why bother? –  Remus Rusanu Feb 18 '13 at 13:42

3 Answers 3

up vote 6 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
    
1GB is not negligible. We have a lot of customers which have no high bandwidth flatrate (even in industrial countries). And sometimes it is necessary to investigate something timely.. 100 MB could be 8 hours. 1GB 3-4 days (if the customer is not using his connection otherwise). –  Offler May 28 '13 at 12:03

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
    
@RemusRusanu You're right, I've edited the answer –  Carol Baker West Feb 18 '13 at 13:55
    
There are very few indexes on this database, the 900 meg that was taken away had the majority of the indexes and data. Should I not theoretically only have the size of the remaining table's indexed left which it will grow by? –  Peter PitLock Feb 18 '13 at 13:56
    
1GB is not negligible. We have a lot of customers which have no high bandwidth flatrate (even in industrial countries). And sometimes it is necessary to investigate something timely.. 100 MB could be 8 hours. 1GB 3-4 days (if the customer is not using his connection otherwise). So size on disc will not help anything. Oh, for SQL Express the shrink brought us multiple times more speed in the past... –  Offler May 28 '13 at 12:04

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

 
discard

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.