shrink datafile in aoag?

• andreas.kreuzberg

  SSCertifiable

  Points: 6191

  More actions

  February 17, 2023 at 10:47 am

  #4150970

  Hi everybody,

  we'd like to migrate one of your biggest databases. But before we could do this, we'd like to shrink the datafile, there are about 700 GB left, which will never be used.

  We'd like to shrink, that we can size the new server a little bit smaler.

  But, can I save time, if I remove the database from the aoag, before I start shrinking?

  I know, if I shrink, I got a lot of fragmentation, but we had to save the disk space on the new system. We tryied so shrink, while the database was part of the aoag, but it happened nothing, we weren't able to shrink 30GB in 30 minutes.

  Maybe someone can give me tips.

  Thanks

  Kind regards,

  Andreas

• Jeff Moden

  SSC Guru

  Points: 1004403

  More actions

  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)