Post reply

shrink datafile in aoag?

  • February 17, 2023 at 4:30 pm

    #4151086

    I can't help on AOAG because I've never had to use it.  Once someone helps you figure that out, I can give you a very strong hint on how to defragment without ending up with a large amount of free space cause by the necessary REBUILDs.

    1. Identify your largest 2 indexes.
    2. Create a new filegroup and file on the database.
    3. Use a CREATE INDEX (identical to the original index) with the WITH (DROP_EXISTING = ON) as well as the new filegroup to move the two indexes to the new file group.
    4. Do your shrink.
    5. Rebuild any indexes that were fragmented by the index inversion of the shrink.
    6. Do the CREATE INDEX WITH (DROP_EXISTING = ON) to move the indexes from the new file group back to the original file group (probably the PRIMARY file group)... but, see the tip below before you make this decision.

    As a bit of a sidebar, if you have large indexes that you need to rebuild on occasion, consider moving each one to its own separate file/filgroup and leave it there.  The next time you need to rebuild it, create another new file/filegroup and do move using the "with drop existing" thing.  Then drop the now empty file group and you won't have any large amounts of unwanted freespace in your database like you normally would.  I simply refer to this method as a "Swap'n'Drop".  It takes some custom coding but it is SO worth it for me especially when it comes to keeping the size down for test restores, necessary (and secure, to be sure) copies of prod, etc, etc.

    --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)

Viewing post 1 (of 2 total)

You must be logged in to reply to this topic. Login to reply