Post reply

dbcc showcontig shows an unexisting index

  • August 11, 2009 at 4:43 am

    #156089

    Hi Everyone

    During my routine checks on our production server i ran following dbcc command on one of the database tables.

    use dbname

    dbcc showcontig ('dbo.tblCompany') WITH ALL_INDEXES, TABLERESULTS, FAST, ALL_LEVELS, NO_INFOMSGS

    The result of the above command writes three records. Two of this records are for legitimate indexes but the thrid index named ''ttblCompany' doesn't seem to exist when i look under table->indexes node in object explorer.

    I have tried running dbcc indexdefrag on this index with following command but that results with an error (see below)

    USE [Acc_Online_DB]

    GO

    DBCC INDEXDEFRAG( N'Acc_Online_DB', N'[dbo].[tblCompany]', N'ttblCompany' )

    GO

    RESULT:

    Msg 2560, Level 16, State 1, Line 1

    Parameter 3 is incorrect for this DBCC statement.

    Can anyone think a reason why this third index exists on the table?

    Thanks a lot in advance.

    Vivek

    Vivek Shukla - MCTS SQL Server 2008

  • August 11, 2009 at 5:08 am

    #1037320

    Further investigation:

    I have checked sysindexes table and this index exists and been used 113 tiems.

    Also, I have found couple of indexes like this and they all have one thing in common which is they all have indid=255???

    does that mean its a system/internal index or something????

    Vivek Shukla - MCTS SQL Server 2008

  • August 11, 2009 at 5:30 am

    #1037326

    Index 255 is the table's LOB data (text, ntext, image, varchar(max), nvarchar(max), varbinary(max)). It's not a real index.

    p.s. since you're on 2005, perhaps start using sys.dm_db_index_physical_stats, ALTER INDEX ... REBUILD and sys.indexes instead of the backweard compat SQL 2000 commands.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 11, 2009 at 5:36 am

    #1037328

    GilaMonster (8/11/2009)

    Index 255 is the table's LOB data (text, ntext, image, varchar(max), nvarchar(max), varbinary(max)). It's not a real index.

    p.s. since you're on 2005, perhaps start using sys.dm_db_index_physical_stats, ALTER INDEX ... REBUILD and sys.indexes instead of the backweard compat SQL 2000 commands.

    Thanks a lot for your help.

    Vivek

    Vivek Shukla - MCTS SQL Server 2008

Viewing 4 posts - 1 through 3 (of 3 total)

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