The reorg command provides four subcommands for carrying out different types and levels of reorganization:
reorg forwarded_rows undoes row forwarding.
reorg reclaim_space reclaims unused space left on a page as a result of deletions and row-shortening updates.
reorg compact both reclaims space and undoes row forwarding.
reorg rebuild undoes row forwarding and reclaims unused page space, as does reorg compact. In addition, reorg rebuild:
Rewrites all rows to accord with a table’s clustered index, if it has one
Writes rows to data pages to accord with any changes made in space management settings through sp_chgattribute
Drops and re-creates all indexes belonging to the table
The reclaim_space, forwarded_rows, and compact subcommands:
Minimize interference with other activities by using multiple small transactions of brief duration. Each transaction is limited to eight pages of reorg processing.
Provide resume and time options that allow you to set a time limit on how long a reorg runs and to resume a reorg from the point at which the previous reorg stopped. This allows you to, for example, use a series of partial reorganizations at off-peak times to reorg a large table. For more information, see “resume and time options for reorganizing large tables”.
The following considerations apply to the rebuild subcommand:
reorg rebuild holds an exclusive table lock for its entire duration. On a large table this may be a significant amount of time. However, reorg rebuild accomplishes everything that dropping and re-creating a clustered index does and takes less time. In addition, reorg rebuild rebuilds the table using all of the table’s current space management settings. Dropping and re-creating an index does not use the space management setting for reservepagegap.
In most cases, reorg rebuild requires additional disk space equal to the size of the table it is rebuilding and its indexes.
The following restrictions hold:
The table specified in the command, if any, must use either the datarows or datapages locking scheme.
You must be a System Administrator or the object owner to issue reorg.
You cannot issue reorg within a transaction.
