• Arsh - Wednesday, August 16, 2017 1:56 PM

    See how much free space you have in the database. If it's huge and if it's sure that it won't be used for a longtime, you can consider shrinking and using that space . It should be the last option and would among the last to consider though.

    Good lord, NO!  😉 Not even as a last option.  The type of fragmentation caused by shrinking an MDF requires that a defrag be done (it's not like the normal rather well behaved fragmentation you would get just from normal usage).  The type of fragmentation done by a shrink WILL absolutely kill performance even for some of the small stuff.  If you do index rebuilds to do the defrag, your MDF will grow by ~120% of whatever your largest index was... which could and probably will be the clustered index on your largest table.  If that's a 30GB object, your MDF will likely grow by 36GB.  If you do reorgs to fix the fragmentation, your log file could easily grow by that much or more depending on how often you do backups, etc.

    There are ways to do a different type of "shrink" but, guess what?  They also require extra disk space until all the data/index pages have been moved.

    What really needs to happen is that Aaron's hardware group needs to work with him to make this possible.  I know it's not free but buying extra hard disk space is a hell of a lot cheaper than spending a man-month two trying to figure out a solution that may simply fail or, worse yet, one that doesn't fail but isn't correct.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)