DBCC DBReindex

Question

Post reply

DBCC DBReindex

SA-1

SSCrazy Eights

Points: 8113
More actions
November 21, 2006 at 7:19 am

#174965

What does DBCC DBReindex do? Does it create a copy of the table and then re-indexes it? or Does it create a copy of the index and does a re-org on it?

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

In the reindex process, existing indices are dropped. The, new indices are created.

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 2

I have the following setup:

DB Size 3.8TB - 2 Datafiles In Primary File Group - (DF1 - 2.20 TB / DF2 - 1.6TB) Free Space on DF1 400GB & DF2 300GB

TBL1 Size 800GB

TBL1_INDX1 Size 1.4GB

Disk Space Available - 72GB

When I tried to do a reindex on TBL1_INDX1 it fails saying that the Primary File Group is full.

If DBCC DBREINDEX drops the index and then recreates it, the job shouldn't have failed.

Any ideas or suggestions.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

What is the recovery mode of your database? When you reindex a database, a large amount of transaction log will be created. You may change the recovery mode to either SIMPLE or Bulk-logged.

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 4

The database is in Simple recovery mode.

Is a copy of the index or table created during the dbreindex process?

SQLBill SSC Guru Points: 51440 More actions · Answer 5

A copy of the index will still be made. If the reindexing is stopped before it finishes, it has to be able to rollback all the changes. So the log file will get huge while it's being done.

-SQLBill

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 6

This is what I googled, to rebuild a clustered index, there should be 1.25 x (table size) available on the disk.

Can anyone validate this?

TIA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Does that include log activity and size required in tempdb???

I just checked my log backups after a reindex and they are at least the size of the DB.

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 8

This would definitely include increase the size of the logs

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

DBCC DBREINDEX creates new indexes before dropping the old ones. This is done as one atomic transaction, so a lot of free space may be required.

If it is rebuilding a clustered index it needs enough space to create a new copy of the table, with a little overhead for non-leaf pages this is where the 1.2 x (avg row size) x (number of rows) recommendation comes from. If it is rebuilding a nonclustered index it needs [(avg index row size) + (clustered key size, or rowid size if no clustered index)] x (number of rows). If it is rebuilding all indexes for a table it needs the sum of the requirements for each index. All of these changes must be logged, so a similar amount of free space is require for the transaction logs.

If you don't have enough space for DBCC DBREINDEX, use DBCC INDEXDEFRAG. It will compact pages and free up some space, but it may not reduce external fragmentation as much as reindexing. It will still require a lot of logging, but it uses many small transactions instead of one huge one.

Another option that is much more disruptive would be to drop constraints & indexes, dump the data with BCP OUT, truncate the table, reload it with BCP IN, and recreate constraints & indexes. Although it is more complicated it may actually be much faster than DBCC DBREINDEX, especially if the BCP file is on a separate disc than the SQL data files. Note that a full backup may be required after a bulk insert, depending on your backup strategy.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 10

you could also pass in the correct parameters for the index to de rebuilt (not the clustered one obviously) rather than reindexing the whole table

???

MVDBA

Pankaj Khanna Say Hey Kid Points: 707 More actions · Answer 11

DBCC DBREINDEX needs 120% more space than the table size. In this space, SQL Server copiers the table, creates tghe index and then, the old index is dropped.

Pankaj Khanna
Database Administrator - SQL Server 2000

Keep hope to keep you intact...

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 12

That is an oversimplification. It needs 1.2 x the size of the data in the table just to rebuild a clustered index. (If there have been a lot of randomly-distributed row deletions, or if randomly-distributed inserts cause a lot of page splitting, this may be much smaller than the current size of the table.) If all indexes for a table are being rebuilt, it needs enough free space to copy the table plus all the indexes. And it needs a similar amount of free log space.

All the rebuilding takes place as one transaction, so enough free space must be available for all the new indexes to be built (and logged) before anything is dropped.