Prerequisites for Running reorg rebuild

You must perform some preliminary steps before running reorg rebuild.

  • Set the database option select into/bulkcopy/pllsort to true.

  • Determine which locking scheme your table uses. You can issue reorg against an entire allpages-locked tables. However, you cannot issue fine grained reorg index or reorg partition level on allpages-locked tables.

  • Make sure that additional disk space, equal to the size of the table and its indexes, is available.

To set select into/bulkcopy/pllsort to true, enter:
1>use master
2> go
1> sp_dboption pubs2, "select into/bulkcopy/pllsort", true
2> go
Following a rebuild on a table:
  • You must dump the database containing the table before you can dump the transaction log.

  • Distribution statistics for the table are updated.

  • All stored procedures that reference the table are recompiled the next time they are run.