Database Shrinking
Ok, talking about shrinking databases is like trolling for comments. And they came today. I’m going to post them here though, because they pretty well demonstrate why it is not really a useful practice to shrink databases. In fact, that was the reason I hadn’t done it on a production system for over a decade.
Here are some comments from our readers:
Nat says
Very good write up on why a shrink would be necessary — when you need to steal space from another db. I’ve had to do this for a small budget client who just couldn’t afford to add more disk space immediately… i’m not sure if anyone explained why shrinking a db is bad…, but it’s really unnecessary if you are shrinking…every night. I have a few analogies, but here’s one.
Say you could magically shrink your stomach every night when you go to bed. But each day you eat and it grows back to the same size. But you know you periodically go to the bathroom and your stomach empty’s out. So at night you ha ve an em pty ‘big stomach’. Why shrink your stomach just to grow it back to fill it again, other than for cosmetic reasons? Instead, just eat as normal and let it fill and empty every day.
This is what a database does. But if you shrink your stomach and grow it every day, problems with your health can occur.. you look good in the morning, but fat at night, so you shrink to look good, but as you do that you mess with your health. Now occasionally, you might go on vacation and eat way more than you do on a normal basis. Then you might say be stuck with this big stomach, and you want to shrink to a size that is normal for you. In the end you don’t gain anything from shrinking your database files.
Corbier writes
Well it’s very easy to create a scheduled maintenance plan that updates statistics, regorganises indexes and rebuilds indexes, as well as shrinking. Keeps everything tidy. Might not work for very many Gigabytes of data, but for smaller databases it does the trick.
Editor’s Note
Schedule jobs to update statictics and re-organize or rebuild indexes is an awesome idea. However, if you shrink your database when you are done, you just ruined all that great work you did re-indexing. Why? I don’t know why they made SQL Server work that way. But it does. Chris points this out with his response, “What do you do about fragmentation?” In short, if you shrink a database after defragmenting your database contents through re-indexing, you just fragmented the tables all over again, and lost the value of the time you performed. Most likely you’ll need the unused space for some other purpose in the future anyway.
A very good practice is to pre-allocate database space ahead of time so that the data files are contiguous on the host operating system. So, shrinking can cause your database files to become fragmented on the OS. This is like a double whammy if the data files are fragmented, and the contents of the data files are fragmented.
For those of you with smaller databases you can keep from growing them much larger by having adequate space in tempdb, and rebuilding/reorganizing your tables using tempdb rather than in the physical data file. If tempdb is big enough, this will keep you from growing your data files and then wanting to shrink your database.
Key concepts:
- Don’t shrink unless you absolutely have to
- Empty, unallocated space in a database is a good thing. Disk space is cheap. Empty space can keep new data from fragmenting as much, and reduce the time to defragment your database
- Pre-allocate disk space to your data files so that the data files are not fragmented on your OS host drives.
- Sometimes it is better to create a new file group for a database, than to extend the existing file group. Otherwise, you may have to stop the database service in order to defragment the database file on the OS disk.
There are many other great tips on disk utilization, indexing, performance tuning using disk and index principles, and managing fragmentation. Get a book on database internals or search for a more detailed coverage of the topic using the SSWUG search. If you are an accidental DBA, this is a great topic for you to dig into ASAP.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Tips for using SQL Server 2012 Query Hints
In this article, you can find some helpful tips to optimize SQL Server 2012 queries by using hints.
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)