Reclaims unused space on pages, removes row forwarding, or rewrites all rows in the table to new pages, depending on the option used.
reorg compact table_name [partition partition_name]
[with {resume, time = no_of_minutes}]
reorg forwarded_rows table_name [partition partition_name]
[with {resume, time = no_of_minutes}]
reorg rebuild table_name [index_name [partition index_partition_name]]
reorg reclaim_space table_name [index_name] [partition partition_name]
[with {resume, time = no_of_minutes}]
combines the functions of reorg reclaim_space and reorg forwarded_rows to both reclaim space and undo row forwarding in the same pass.
removes row forwarding.
is the name of the index partition on which you are running reorg. update statistics peforms a check to validate that index_partition_name is an index partition. If you specify an index partition, only that index partition is rebuilt
specifies the name of the index to be reorganized.
is the name of the partition on which you are running reorg.
specifies the name of the table to be reorganized. If indexname is specified, only the index is reorganized.
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 the table has an index, all indexes are dropped and re-created. If an index name is specified, reorg rebuilds that index while leaving the table accessible for read and update activities.
reorg rebuild is not supported for
system catalogs.
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.
reorg reclaim_space only
affects tables with variable-length rows, and only frees
up space within a page. To reduce the number of pages used, use
the reorg rebuild command.
initiates reorganization from the point at which a previous reorg command terminated. Used when the previous reorg command specified a time limit (with time = no_of_minutes).
specifies the number of minutes that the reorg command is to run.
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
Runs reorg forwarded_rows on the smallsales partition of the titles table:
reorg forwarded_rows titles partition smallsales
Runs reorg forwarded_rows on the authors table:
reorg forwarded_rows authors
Runs reorg reclaim_space on the bigsales partition of titles:
reorg reclaim_space titles partition bigsales
Runs reorg compact on the bigsales partition of titles:
reorg compact titles partition bigsales
Runs reorg rebuild on the index partition idx_p2 of index local_idx on table sales:
reorg rebuild sales local_idx partition idx_p2
The table specified in reorg—excluding reorg rebuild—must have a datarows- or datapages-locking scheme.
Index scans traverse faster after you run reorg.
Running reorg against a table can have a negative effect on performance of concurrent queries.
If you do not include the index or partition name, the entire table is rebuilt.
You can perform a dump tran on a table after rebuilding its index. However, you cannot perform a dump tran if the entire table has been rebuilt.
Although online index rebuilding is allowed on a placement index, it rebuilds only the index pages. The data pages remain untouched, which means datarows are neither sorted nor rewritten to fresh pages. You can rebuild data pages by dropping a placement index, and then re-creating it.
You can rebuild the index for systabstats, but you cannot run reorg rebuild on the table itself.
You cannot run reorg on text that has an indid of 255 in sysindexes.
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.
Requirements for using reorg rebuild on an index are less stringent than for tables. The following rules apply:
You do not need to set select into to rebuild an index.
Rebuilding a table requires space for a complete copy of the table. Rebuilding an index works in small transactions, and deallocates pages once they are copied; therefore, the process needs space only for the pages copied on each transaction.
You can rebuild the index on a table while transaction level scans (dirty reads) are active.
The following considerations apply when using the resume and time parameters:
If you specify only the resume option, the reorg begins at the point where the previous reorg stopped and continues to the end of the table.
If you specify only the time option, the reorg starts at the beginning of the table and continues for the specified number of minutes.
If you specify both options, the reorg starts at the point where the previous reorg stopped and continues for the specified number of minutes.
ANSI SQL – Compliance level: Transact-SQL extension.
You must be a System Administrator or the object owner to issue the reorg command.
Documents For more information, see the System Administration Guide.
