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.

I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I delete 50 GB of data -- not on one table, but a general pruning of old data on a database wide level, covering 90% of the tables -- does this constitute an appropriate use case for shrinking the database?

If not, what are the appropriate steps to take to clean house after removing such a high percentage of data from a database? I can think of two: Rebuild Indexes and Update Stats. What else?

share|improve this question

4 Answers 4

up vote 7 down vote accepted

A reorganise-and-shrink is never recommended really.

If you can take the apps the database is serving offline, you can speed up the process and reduce index fragmentation by removing all indexes and primary/foreign key constraints before the shrink (this will mean there is less data to be moved around as only the data pages will be shuffled not the now non-existent index pages, speeding up the process) then recreate all the indexes and keys. Recreating the indexes after the shrink means they should not be significantly fragmented, and having them gone during the shrink means rebuilding them won't leave many small "holes" in the page allocation within the files that may invite fragmentation later.

Another option if you can offline the applications is to migrate all the data to a fresh database of the same structure. If your build process is solid you should be able to build that blank DB quickly, if not create one from the current DB (restore a backup of the current one, truncate/delete all the contents in the tables and perform a full shrink). You might still want to drop all the indexes in the destination and recreate them afterwards as this can be a lot more efficient when changing a lot of the indexed data (100% of it in this case). To speed up the copy process, have the datafile(s) of the destination database on different physical drives to the source (unless you are using SSDs in which case you don't need to care about reducing head movements), you can move them to the source location when you are done. Also, if creating the destination as new (rather than by blanking a copy of the source) create it with an initial size that will contain all the current data plus some months worth of growth - that will make the data copy a little faster again as it won't be allocating new space every now and again throughout the process.

Edit: (as per longneck's comment, I didn't say why this might be better than using shrink)

Migrating the data to a fresh database replicates the intended action of the shrink operation, but potentially with far less fragmentation (which is the unintended consequence of a reorganise-and-shrink). A shrink simply takes blocks from near the end of the file and puts them in the first space nearer the beginning making no effort to keep related data together.

I suspect the result will be more efficient space-wise too as there is likely to be less part-used pages afterwards. A shrink will just move part-used pages around, moving the data is more likely to result in full pages especially if you insert into the destination in PK order and create other indexes after the data has all migrated.

Of course if you can't take the apps offline at all, just performing a shrink is your only option so if you really need to reclaim the space go with that. Depending on your data, access patterns, common working set size, how much RAM the server has, and so forth, the extra internal fragmentation may not be all that significant in the end.

share|improve this answer
1  
Why is this any better than a shrink? if all of this replicates what a shrink does, then why go to all the trouble of modifying database structures? I'm not saying this is a bad answer, but you failed to explain why this is better than a shrink. –  longneck May 9 '12 at 21:42
    
@longneck: Good catch, I've added my thoughts on that to the response. –  David Spillett May 12 '12 at 10:57
    
You didn't say what you would recommend as a copy operation. SSIS? INSERT INTO ... SELECT? –  Tola Odejayi Jun 25 at 18:20
    
Either SSIS or base TSQL would work just as well (the SSIS option might be less efficient, but potentially easier to maintain later). If you create the FK relationships at the end along with the indexes you can do a simple "for each table, copy" in either case. Of course for a one-off as described above a shrink+reorganise is probably fine too but I just like to scare people into never considering regular shrinks! (I've known people schedule them daily) –  David Spillett Jun 26 at 9:11

Is the database going to grow again? If so then the effort you're going to put into the shrink operations are just going to be a waste, because when you've got the file size down and then you add more data, the file will just have to grow again, and transactions have to wait for that growth to happen. If you have sub-optimal auto-growth settings and/or a slow drive this growth activity is going to be quite painful.

If you do shrink the database, what are you going to use the freed up disk space for? Again if you are just going to keep that space free in case this database grows again, then you're just spinning your wheels.

What you might consider doing, now that you've got all this free space in the file, is rebuilding your indexes so that they are better optimized (and it will be much less painful to do this when you have free space to do so - think about trying to change a sweater in a tiny closet vs. a big bedroom).

So unless this was a major cleanup operation and you really won't be ramping up to the same level of data again, I'd just leave it as is and focus on other areas of optimization.

share|improve this answer
    
@Aarron Bertrand Well it took 10 years to get that big and disk is a bit of a concern as I'd like to put it on solid state. I was thinking of shrinking down to 60gb with a 5gb autgrowth. Really the only thing you recommend would be to rebuild the indexes, huh ? I thought people would have some more recommendtations. –  bumble_bee_tuna May 1 '12 at 2:09
    
And I would only recommend the rebuild if they need it. But I would do that before you shrink the file. Can't really think of anything off the top of my head that you would do with some free space that would provide performance optimizations in the general case... –  Aaron Bertrand May 1 '12 at 2:10

If you are running out of space, and your data isn't supposed to get that big then shrink, but rebuild your indices after with appropriate fill factors that allows for typical growth.

If your end goal is actually to reduce backup size make sure you implement a comprehensive backup strategy to clear out the transaction log and when you back up the db, use the compress options.

I wouldn't recommend auto growth of 5gb unless you typically will expect to grow 5gb frequently. You could have intermittent performance problems otherwise. Your data size should first be set to what you think is required for example, a year, and Auto growth should be set to a size that you've tested doesn't affect operating performance.

http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/

share|improve this answer
    
And Aaron, rebuilding indexes before shrinking causes the indexes to be badly laid out. Its not good to rebuild then shrink. - someone correct me if I'm wrong. –  Gilesey May 1 '12 at 7:11
    
I meat shrinking causes the indexes to be mangled to recover space - so rebuilding beforehand then shrinking is pointless. thomaslarock.com/2009/03/when-to-use-auto-shrink –  Gilesey May 1 '12 at 7:24
    
Remind me never to use an iPhone to reply to a post again. Auto correct hell. Meat=meant –  Gilesey May 1 '12 at 7:37
    
If you shrink then rebuild indexes, the data file is just going to have to grow again in order to accommodate the copy of the data used to rebuild. While it won't be as large as the original data file in this case, it will still be growth and it seems counter-productive. Rebuilding while there is free space will be faster (no auto growth required) and will generally still be better than you suggest about how it lays out the pages for the new copy of the index, and I suspect in most cases this will overall be shorter and lead to same or better disk space recovery. Perhaps time for some tests. –  Aaron Bertrand May 1 '12 at 11:38
    
And of course this is assuming the indexes on the data that remains will actually need to be rebuilt - maybe they're already in pretty good shape. –  Aaron Bertrand May 1 '12 at 11:40

I don't know if this would work better than reindexing after the shrink but another option would be to create a new data file that's appropriately sized and move all the data to that. In that case I would do a reindex first so you know what the actual data size is. One catch is that if this is the first file in the primary data file I don't think you can empty it. You should be able to shrink it then move the data back afterwards and that would avoid the page reversal. However, if you're looking at moving to solid state that shouldn't make a big difference anyway.

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.