Reclaims unused space on pages, removes row forwarding, or rewrites all rows in the table to new pages, depending on the option used.
reorg reclaim_space tablename [indexname]
[with {resume, time = no_of_minutes}]
reorg forwarded_rows tablename
[with {resume,time = no_of_minutes}]
reorg compact tablename
[with {resume, time = no_of_minutes}]
reorg rebuild tablename [indexname]
reclaims unused space left by deletes and updates. For each data page in a table, if there is unused space resulting from committed deletes or row-shortening updates, reorg reclaim_space rewrites the current rows contiguously, leaving all unused space at the end of the page. If there are no rows on the page, the page is deallocated.
specifies the name of the table to be reorganized. If indexname is specified, only the index is reorganized.
specifies the name of the index to be reorganized.
initiates reorganization from the point at which a previous reorg command terminated. Used when the previous reorg command specified a time limit (time = no_of_minutes).
specifies the number of minutes that the reorg command is to run.
removes row forwarding.
combines the functions of reorg reclaim_space and reorg forwarded_rows to both reclaim space and undo row forwarding in the same pass.
if a table name is specified, rewrites all rows in a table to new pages, so that the table is arranged according to its clustered index (if one exists), with all pages conforming to current space management settings and with no forwarded rows and no gaps between rows on a page. If an index name is specified, reorg rebuilds that index while leaving the table accessible for read and update activities.
Reclaims unused page space in the titles table:
reorg reclaim_space titles
Reclaims unused page space in the index titleind:
reorg reclaim_space titles titleind
Initiates reorg compact on the titles table. reorg starts at the beginning of the table and continues for 120 minutes. If the reorg completes within the time limit, it returns to the beginning of the table and continues until the full time period has elapsed:
reorg compact titles with time = 120
Initiates reorg compact at the point where the previous reorg compact stopped and continues for 30 minutes:
reorg compact titles with resume, time = 30
The table specified in reorg must have a datarows or datapages locking scheme.
You cannot issue reorg within a transaction.
reorg rebuild requires that you set the database option select into/bulkcopy/pllsort to true and run checkpoint in the database.
reorg rebuild requires additional disk space equal to the size of the table and its indexes. You can find out how much space a table currently occupies by using sp_spaceused. You can use sp_helpsegment to check the amount of space available.
After running reorg rebuild, you must dump the database before you can dump the transaction log.
For more information, see the System Administration Guide.
SQL92 – Compliance level: Transact-SQL extension.
You must be a System Administrator or the object owner to issue the reorg command.
System procedures – sp_chgattribute
