When we delete huge amounts data from a table, what should be the best thing to follow: Shrink the file group first and rebuild indexes, or Rebuild indexes then shrink the file group?

  • Honestly, you probably shouldn't be doing either of those operations. They're a waste of server resources, especially if you plan to do them regularly.
    – J.D.
    Aug 12, 2022 at 19:23

1 Answer 1


Shrink then rebuild. Because

  1. Shrink introduces fragmentation that rebuild removes. And

  2. Shrink doesn't leave the right amount of free space in the files, but the amount of new space required by the rebuild is a reasonable buffer to minimize file growth during normal operations.

  • 1
    An online rebuild will require double the amount of space though. And you can specify a size to shrink to. Aug 12, 2022 at 18:23
  • What if the data I have deleted is huge ..for example i maintain huge yearly data, Ih I delete previous year data and i dont have required amount of space for rebuild even after shrink..what should I do in such cases
    – Krishna508
    Aug 12, 2022 at 18:44
  • @Charlieface Offline rebuild requires space for old and new index + sort space (unless sort_in_tempdb). The old index is dropped at the end of the rebuild. Online additionally requires change tracking data structures. And shrink after rebuild might introduce fragmentation. Aug 12, 2022 at 19:44
  • @Krishna508 if don't have space for a clustered index rebuild, you can disable non-clustered indexes before the rebuild. And if that doesn't work you can reorganize instead. Aug 12, 2022 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.