Post reply

dbcc showcontig - why does it show an index that doesn't exist?

  • May 4, 2008 at 5:26 pm

    #380308

    I ran dbcc showcontig on a SQL 2000 database table:

    DBCC SHOWCONTIG('db.mytable') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

    and it returned two rows, the first for the clustered index, and the second for an ordinary index. Both are present in the sysindexes table.

    Yet if I bring up the detail on the table in Enterprise manager it only shows the clustered index.

    I've tried running dbcc checktable(mytable) and run dbcc updateusage on both indexes, and these complete with no errors.

    I've run DBCC DBREINDEX('mytable', 'myindex', 0) and both complete without error.

    The problem is that I'm using dbcc showcontig to identify indexes that are fragmented so I can run DBREINDEX on them. dbcc showcontig shows that the non-existant index has 99% fragmentation under the "LogicalFragmentation" column, which flags it up as needing to be defragmented (the clustered index is 0%). This is a problem because as there are 30000 tables in this database I can't check which ones are real indexes and which are non-existant indexes, so I can't use showcontig to tell me what is the level of fragmentation in the database with any reliability.

    The table is small, containing 700 rows.

    Any thoughts?

    Thanks

    Edit: I see this is very similar to a question posted by Tim Peters below. But I thought I'd give it its own thread.

    http://90.212.51.111 domain

  • May 4, 2008 at 9:43 pm

    #810502

    Post the name of the supposedly non existant index, please.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 5, 2008 at 6:45 am

    #810631

    Hi,

    The "non-existant" index name is: tIWB0CONT1

    The clustered index name is: IWB0CONT1~0

    The table name is: IWB0CONT1

    It is the database back-end of a SAP system.

    Thanks

    http://90.212.51.111 domain

  • May 7, 2008 at 2:43 am

    #811627

    Any ideas?

    http://90.212.51.111 domain

  • May 8, 2008 at 1:11 am

    #812254

    Sorry for the delay, Neil...

    No, those don't look like the stats or system generated indexes I was expecting. I don't know on this one... my appologies.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 8, 2008 at 5:19 am

    #812340

    Jeff Moden (5/8/2008)

    Sorry for the delay, Neil...

    No, those don't look like the stats or system generated indexes I was expecting. I don't know on this one... my appologies.

    no worries

    http://90.212.51.111 domain

  • June 30, 2008 at 8:43 am

    #836173

    Hi Neil,

    Is your index in question prefixed by a t? I had the same issue this morning where a showcontig was returning indexes that I couldn't see through Enterprise Mgr.

    After looking into it further, I found that anything in sysindexes with an indid of 255 are holders in sysindexes for text, ntext and image data that exist on your table. These cannot be dropped as SQL will complain about it being a system index.

    Hope this helps.

    Cheers,

    Dave

  • June 30, 2008 at 8:57 am

    #836186

    Thanks for that... good to know! 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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