• GabyYYZ (3/16/2010)


    That being said, there are a few absolutes you can give for SQL Server:

    1. Do NOT turn autoshrink on.

    2. ummm...

    3. ummm...

    Come to think of it, as far as I can remember, # 1 seems to be the only answer about SQL Server that all experts seem to agree on...I can't think of any others. Please tell me if I'm wrong and there are some other obviously bad practices that are not much open to debate (or is someone willing to defend keeping Autoshrink on?) 🙂

    Cheers.

    Well, I'll risk being called a nutcase and suggest that even for Autoshrink the answer is "it depends".

    Suppose you have a fairly small database, with occassional large insertions and large deletions that leave the total data size the same but the space occupied much greater (page splits all over the place); now suppose also that most of the time this database is read only; add to that the idea that if you leave autoshrink on the database will be small enough that it all fits into SQL Server's RAM on the server you ar running on, so that disc accesses are non-existent/negligible except during the occassional big updates, but if you leave autoshrink off the database will not fit into RAM unless you do some explicit shrinking. No-one has ever managed to explain to me just what benefit turning auto-shrink off in those circumstances will deliver, and how this benefit outweighs the obvious disbenefits.

    So, as for pretty well everything else in the DB world, the answer is "it depends". Almost always it is right to have autoshrink off. In some very rare circumstances it is right to have it on. It depends on the shape of your workload, the size of your DB, whether or not the size of the DB can be regarded as effectively constant, what else is on the server, and so on.

    There is one thing you really can be certain of though. If someone has a maintenance job that goes around rebuilding the indexes in the db every night and also has autshrink on in that DB then they really did get it wrong (I think all the experts agree on that one). Only slightly less certain is that all experts would agree that using autoshrink to save disc space is always wrong (I believe that all experts agree on that because because I think that anyone who doesn't can't be an expert; but I have to accept that I could be wrong)

    Tom