Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    UK
    Posts
    26

    Unanswered: DBCC DBREINDEX does nothing

    Has anyone had the problem with DBCC DBREINDEX on a table/index not doing anything.
    You just get the usual "DBCC execution completed. If DBCC printed error messages, contact your system administrator." message.
    I have tried:
    DBCC DBREINDEX ('<table_name>')
    DBCC DBREINDEX (<table_name>)
    DBCC DBREINDEX ('<table_name>', '')
    DBCC DBREINDEX ('<table_name>', '<index_name>')
    DBCC DBREINDEX ('<table_name>', '<index_name>', 80)

    Maybe its "optimising" and not thinking anything needs updating.

    Should I look for an alternative way of rebuilding an index like dropping and recreating?

    The beauty with DBCC DBREINDEX ('<table_name>') would have been that I was going to get the list of user tables in the database and call it for each table to simply rebuild all indexes.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    For small tables (up to around 1000 pages), DBCC is pretty quick. Your hardware will also determine the speed of the operation, as well. In order to see if DBCC is actually doing anything, you should run dbcc showcontig before and after to see if the density has changed. Note also for very small tables (under 8 pages), DBCC DBREINDEX has no effect. Neither does drop/rebuild of indexes. These tables are simply too small to actually have the data rearranged.

  3. #3
    Join Date
    Mar 2004
    Location
    UK
    Posts
    26

    I thought you got extra lines...

    From the help it suggests you get something like:

    Index (ID = 1) is being rebuilt.
    Index (ID = 2) is being rebuilt.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I have never seen those messages (Index (ID = 1) is being rebuilt). Doesn't seem to return messages even with trace flag 3604 turned on. The article says it was updated July 2003, too. Looks like you may have found a bug in the documentation.

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I also do exactly what you mention (do the reindex on all tables in my database from the system list of user tables) on a weekly basis, and just get the summary data that you mention in your posts. I have never seen the interim "status" message in my reindex logs, and as mentioned perform the operation on my complete database (approx 35 tables) on a weekly basis.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Mar 2004
    Location
    UK
    Posts
    26

    I'll have to do it another way then...

    It looks like DBCC DBREINDEX isn't the answer.
    In my case I need to drop and recreate the indexes for them to be properly rebuilt.
    While I can write a script with an entry for every single index this is hard work as there are a lot of indexes.

    I really would like to write a script to loop through the tables, get the index info and drop and recreate them in a single statement.
    The problem with this is that the database has foreign keys.
    Therefore tables need to be done in a certain order.
    If you drop an index a foreign key is based on, it naturally produces an error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •