Using DBCC DBReindex

Increasingly, you can count on most DBCC commands as being deprecated in future versions of SQL. This is one of the ones that will be removed later.

It still servers a purpose. However it can be replaced using the ALTER INDEX command.

The great thing about the DBCC DBReindex command is that it will completely rebuild a totally fragmented or corrupted index. This is likely to happen on a busy production system.

The most basic syntax is:
[cc lang=”sql”]
DBCC DBREINDEX (‘Person.Address’)
This will rebuild all indexes on the Person.Address table with the default fill factor. Optionally, you can also re-index only one index on the table and specify a specific fill factor. To do that, simply add the following two parameters:
[cc lang=”sql”]
DBCC DBREINDEX (‘Person.Address’, ‘PK_Address_AddressID’, 90)
This will only re-index one index, in this case the primary key, with a fill factor of 90%. Meaning 90% of the data pages will be full, leaving 10% for splits.

Something to note with DBCC DBReindex is that it will take your operation offline. Meaning it will block any operations that occur against the table during the duration of the rebuild. There is a way around this. It is to use the ALTER INDEX command.

Here is the alternative.

[cc lang=”sql”]

This command will rebuild all the indexes on the Person.Address table with a fill factor of 90%. This will all happen online, meaning that the rebuild will not block existing processes.

This is the best way to rebuild an index using SQL 2005+

Baxter Basics 15 Dec 2010 at 6:40 am

Please note that the ONLINE=ON option is only available in the Enterprise edition of SQL server. For users of Express editions etc., you’ll have to use ONLINE=OFF. Not a big deal really.

Derek Dieter 20 Dec 2010 at 3:00 pm

Thanks Baxter

gs 13 Dec 2010 at 9:08 pm

Can tables in replication be rebuilt like normal command you mention above?

Derek Dieter 20 Dec 2010 at 3:02 pm


Unfortunately not. You either need to use the wizard or you can script out the results of the wizard. To do this, perform all the actions you need to perform when re-enabling replication, then select the black arrow next to the “script” button at the top. Then select script to -> New query window. This only works in SQL 2005+

Good luck,

Featured Articles

 Site Author

  • Thanks for visiting!