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.

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx

http://www.sqlskills.com/blogs/paul/post/Auto-shrink-e28093-turn-it-OFF!.aspx

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.

Ever.

11 thoughts on “When To Use Auto Shrink”

  1. Back when I was responsible for the whole Core Storage Engine at MS, I tried to have auto-shrink removed from the product – and failed. Backwards-compatibility trumped the ability for DBAs to shoot themselves in the foot. At least I managed to remove the ‘Repair minor corruptions’ options from the maintenance plan wizard… Cheers

    Reply
  2. Lol, its an option for databases storing historical data sets that other people have access to that might do a query causing a large file size jump, and maybe you have hundreds of these databases on a single server because your cheap and if they all were allowed to expand the server wouldn’t hold them all in thier ‘unshrunk’ state =P. SCADA databases are a good example of where AutoShrink might be useful im currently looking into it as we would like to continue using SqlExpress which has a 4 gig limit. And we will be doing monthly queries on the data, so performance is not a requirement.

    Reply
  3. I think the only situation in which I can recommend this one similar to our shop – because of audit requirements we need to keep a copy of the data at each step of the import/append/analyze process. Of course we are pretty vertical in our processing and availability is not so much of a factor.

    With several “mirror” databases supporting the main analysis process, shrinking any transaction logs and datafiles in these dbs before archiving is essentially a requirement – the integrity of the data tables themselves is all that really matters.

    Likewise discrete analyses are done in separate databases extracted from an appended set. In these DBs many process tables are created that are not required for the final reporting, and likewise a number of statistics and indices are generated that will not be used after the reporting is complete. The overhead objects can be lost entirely, and the space released before detaching and archiving.

    While this is a very atypical scenario – if you work in an environment where the ability to reproduce exactly a certain result set is necessary this can be fairly bullet proof, but it DOES eat up quite a bit of storage space – so removing the unnecessary information and then compacting the files as much as possible is sort of an implicit requirement.

    And as Brant says – every once in a while someone might need to do a query on one of these historical sets which has not yet been shipped off to archive.

    Reply
  4. So if you delete or redact massive amounts of data from a column in your lower environments, you’re just supposed to be happy that the inefficient SQL Server design continues to claim that disk space?
    When is Microsoft going to address this dog-ass design?
    “Drive space is cheap” does NOT address budgetary issues or government regulations.

    Reply
    • Michael,

      Thanks for the comment. This blog post is almost 8 years old now. It could use some updating.

      There are some valid reasons for wanting to reclaim disk space. The scenario I wrote about here is not one of them. The scenario you mention could be one, sure.

      I don’t believe that this is a fault of the SQL Server design. I’m not certain I’d want a RDBMS to be actively trying to prune space, that seems to me to be more overhead than necessary. I would also consider the issue of fragmentation, but with flash storage fragmentation isn’t much of an issue anymore.

      Thanks again for the comment. I should consider writing an updated post.

      Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.