Forum Stats

  • 3,768,263 Users
  • 2,252,769 Discussions
  • 7,874,510 Comments

Discussions

Categories

Is rebuilding the indexes necessary after shrinking a table?

User_OPD8R
User_OPD8R Member Posts: 12 Green Ribbon
in General Database Discussions

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?

· Share on TwitterShare on Facebook

Answers

  • JohnWatson2
    JohnWatson2 Member Posts: 4,327 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.

    · Share on TwitterShare on Facebook