27 Mar When To Use Auto Shrink
Never, that’s when.
Don’t want to believe me? Well then, who would you believe? How about the SQL Craftsman, a man with more MVP-ness than myself.
What’s that? Not good enough for you? Well, then, let me introduce you to Paul Randal. He had some thoughts on the subject.
So why is this still an option? I don’t know. But I submitted some feedback to Connect and you can go and vote on it if you so desire. With any amount of luck we could simply have the option removed from future versions of MS SQL. Please note that I am not asking for the functionality to be removed entirely. No sir, I just want the option removed from the standard Maintenance Plan wizard and tasks. If a shrink is necessary than an experienced DBA should still be able to use the underlying system functions to shrink the data files when necessary. I am just trying to help novice DBA’s from performing actions that will often hurt performance without their knowing.
Now, if you do have the need to shrink a database then you should understand that it will be necssary to rebuild your indexes immediately afterwards. This will often lead to an increase in disk space usage, which is why you were shrinking the database to begin with anyway, right? So if you go through the motions of shrinking a database, then rebuilding your indexes, and you are back to the same size as you were before, you should immediately realize that the time you just spent is lost forever.
We recently had someone recommend that we shrink a database to reclaim some space on disk. The database has roughly 180Gb of space allocated, and about 15Gb of free space. The database is also having its transaction logs shipped across the WAN. The support person from…let’s call it “Small and floppy”, was telling us to shrink the database because we were concerned about the current growth rate and the fact that we are running out of disk space. Seems logical, right? Well, I only had a few questions.
How long will that shrink take to complete? (they had no idea)
When it is done, and I rebuild the indexes, how much space will it chew back up? (they had no idea)
Let’s assume that we could reclaim all 15Gb, are we certain that the growth of the database has leveled off? Or will we find ourselves right back here in six months? (they had no idea)
Since all of those questions were unanswered the first thought I had was “Is there an actual DBA nearby that could help you understand what it means when you auto-fragment the database?” But none of that really mattered because the third question is the one that forced their hand. They needed to spend more time determining if the system was working as designed or not before telling us to shrink. Turns out there is a bug, but the bug is not going to help answer the third question anyway.
And you know what? I still don’t think I will be shrinking that database anytime soon. That just does not seem like it is the right solution.