Forum Stats

  • 3,758,188 Users
  • 2,251,351 Discussions
  • 7,870,103 Comments

Discussions

Is rebuilding the indexes necessary after shrinking a table?

Hello


I want to apply a table shrink to some tables in my PROD database. (The tables are huge)

Should i rebuild the indexes of the tables after the shrink command gets completes?

Or this is not a mandatory task?!


And also....The environment that i will do the shrink is a production environment, so would it be better to execute the shrink command during night hours or it can be done also during the working hours?

Answers

  • JohnWatson2
    JohnWatson2 Member Posts: 4,315 Silver Crown

    There is no need to rebuild indexes afterwards.

    You can start the shrink any time. It shouldn't impact on other work: It is just a set of transactions against the table as it moves the rows. If the extra workload does affect your users, just interrupt it and start it again later. It will, in effect, pick up from where it left off.

    The final stage of the shrink, where the high water mark of the table is moved, will hang if there is an active transaction on the table while it waits for a mode 6 TM lock. So long as your transactions are all short, that isn't a problem. If that worries you, you can interrupt it and try again at a quiet time.