SQL DBREINDEX

• RGP

  SSCarpal Tunnel

  Points: 4381

  More actions

  November 12, 2008 at 2:09 am

  #124297

  Hi,

  Just need to clarify few doubts regarding DBCC DBREINDEX

  1) What does DBREINDEX do?

  My suggestions:

  1) Fill factor space increases, when reindex is done

  2) Data no longer fragmented.

  Are the above two the basic operation done by DBREINDEX ?

  thanks

• John H Marsh

  SSChampion

  Points: 11671

  More actions

  November 12, 2008 at 2:44 am

  #897389

  Hello,

  The indexes are rebuilt to reflect the FillFactor specified (or the original FillFactor if none supplied). So that doesn’t necessarily mean an increase in FillFactor.

  Take a look at the following link (it’s for SQL 2000, but should still hold true for SQL 2005):

  http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

  Regards,

  John Marsh

  www.sql.lu
  SQL Server Luxembourg User Group

• Irish

  Hall of Fame

  Points: 3958

  More actions

  November 12, 2008 at 7:00 am

  #897484

  DBCC DBREINDEX drops each index on the given table, then rebuilds them using the parameters supplied (fill factor) unless none is specified and then it will use the original fill factor when the index was created.

  I have been suggesting to folks who have been traditionally using DBCC DBREINDEX to start using the ALTER INDEX REBUILD command when moving to SQL 2005. It is true that DBCC DBREINDEX will do the same thing, but ALTER INDEX has options that are not available in the DBCC.

  Check 2005 BOL for more details ...

  Regards, Irish 

• The_SQL_DBA

  SSChampion

  Points: 10494

  More actions

  November 12, 2008 at 11:50 am

  #897708

  DBCC increases the log file size, you need to watch for it. If your table size is too big then log will grow to a substantial amount. Better switch to Bulk_Logged recovery mode and the ReBuild Indexes.

  Fill factor depends on the frequency of DML's on your table. If the table data is static then 100% fill factor is good if not choose a lower value.

  Now, if you were to stop a DBCC DBReindex process then the process would be rolled back completely.

  An alternative for this is DBCC INDEXDEFRAG which when stopped will commit the re-indexing it did the time it stops. As said in above posts do some digging in DBCC DBREINDEX or ALTER INDEX commands..

  Thanks!!

  The_SQL_DBA
  MCTS

  "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."