I’m a self-proclaimed communicator (although most people say that I was born a teacher) which is why I always think of ways to simplify concepts, though they seem to be complex.

I was on IM with my former student and asking him how he was doing. During the course of the conversation, I asked if they have a full time DBA. Unfortunately, they don’t and that they rely on their ERP system’s tuning tool to do the performance tuning for their databases (of course, I recommended hiring my services to do it for them). One question that popped up during the conversation was shrinking the database. Most of the time, I get tempted to refer them to SQL Server MVP Tibor Karaszi’s blog about why you shouldn’t be shrinking your databases which I have done a lot of times when answering questions in the technical forums. But this time, I approached it differently because I was this guy’s former trainer and he definitely knows that I will try to keep things as simple as possible.

I started out with the concept of internal and external fragmentation, explaining what happens to the database files when you shrink them and, as auto growth kicks in due to the additional amount of data being loaded, grow back to a large file size. Imagine a room full of boxes where each box contains a bunch of items – shoes, books, other personal items, etc. Of course, a box has a fixed volume that can only contain a certain volume of items, much so occupying a certain amount of space in the room. If you need to add more items in a box full of books, what would you do? Well, you would have to get a bigger box to replace the smaller one before you can hold the additional books. So, the process of adding more books in a full box definitely requires more effort even before you can do the only task you thought about doing – storing books in a box. I bet it would take more time and effort storing books in a full box as compared to storing them in an empty one. Think of this box as your database.

While we cannot auto grow boxes due to the underlying principles of nature, we can do so with databases and any files stored in the file system. However, the process of storing books in a full box is similar to adding more data in a full database. The database engine will have to grow the database even before it can add the data in it. But isn’t the goal just to add records? This means that we are making the database engine do more unnecessary work than we need it to. And you wonder why database professionals complain about transactons getting blocked when the database is already full. Which is why the best practice is to allocate more than enough space on your databases so that we minimize those auto growth events during regular business hours.

And this is where Tibor explained about internal fragmentation and how shrinking your databases will fragment your indexes, further hurting your database performance. Now, what about external fragmentation? Let’s get back to that box analogy. If you were to replace the small box with a bigger one, wouldn’t you move the other boxes around just to accomodate the new, larger box? Imagine that the smaller box is on the third column, fourth stack of the fifth row and that you have organized the boxes in the room in such a way that you have maximized the space (by the way, I’m a bit like that when arranging my personal stuff). You would then have to take the old, smaller box out of it’s original location, find a place where you can place the new, larger box and, finally, store the additional books. If the new, larger box fits in the same place as your old, smaller box, then, you would have unused space in the room and, therefore, not maximizing the room space. This, from a file system and disk perspective, is called fragmentation and from the database’s perspective, external fragmentation. And, because the boxes (or files for disk subsystems) are not arranged in contiguous, close-together kind of fashion, you (or the disk) will have to work harder just to get access to different boxes stored inside the room.

So, the next time somebody asks about why database shrinking is definitely not a good idea, you can tell him this story.