Post reply

Can't find index even though it exists

  • April 15, 2011 at 9:07 am

    #249047

    I'm trying to use sys.dm_db_index_physical_stats to find which indexes are fragmented enough to either be rebuilt or just reorganized. However, I am running into an issue where I get an error saying an index can't be found but the index is there.

    here is the code that will be run: ALTER INDEX [IX_mms_cs_linkobject_idis_renamed] ON [dbo].[mms_cs_link] REBUILD

    Here is the error message:Msg 2727, Sev 11, State 1, Line 1 : Cannot find index 'IX_mms_csmv_linklineage_date'

    Why can't the index be found in order to be rebuilt?

  • April 15, 2011 at 11:38 am

    #1312285

    Laura Schmalzbauer (4/15/2011)

    I'm trying to use sys.dm_db_index_physical_stats to find which indexes are fragmented enough to either be rebuilt or just reorganized. However, I am running into an issue where I get an error saying an index can't be found but the index is there.

    here is the code that will be run: ALTER INDEX [IX_mms_cs_linkobject_idis_renamed] ON [dbo].[mms_cs_link] REBUILD

    Here is the error message:Msg 2727, Sev 11, State 1, Line 1 : Cannot find index 'IX_mms_csmv_linklineage_date'

    Why can't the index be found in order to be rebuilt?

    Just looking at what you've pasted here, these are two different indexes.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • April 15, 2011 at 1:40 pm

    #1312326

    yes it is but it's giving me the same error. So for this index: ALTER INDEX [IX_mms_cs_linkobject_idis_renamed] ON [dbo].[mms_cs_link] REBUILD, I got this error: Msg 2727, Sev 11, State 1, Line 1 : Cannot find index 'IX_mms_cs_linkobject_idis_renamed'

    I just had a typo

  • April 15, 2011 at 2:04 pm

    #1312339

    What does this return?

    SELECT index_id, name FROM sys.indexes WHERE object_id = OBJECT_ID('mms_cs_link')

    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

  • April 15, 2011 at 2:09 pm

    #1312342

    IndexID Name

    0 NULL

    2 IX_mms_cs_linkobjectstateattribrefere

    3 IX_mms_cs_linkrefereobjectattribstate

    4 IX_mms_cs_linkobject_idis_deleted

    5 IX_mms_cs_linkobject_idis_renamed

    and the index that is giving me issues is index_id = 5

  • April 16, 2011 at 12:40 am

    #1312414

    Is it disabled? Hypothetical? (check the other columns in sys.indexes)

    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

  • April 16, 2011 at 2:06 pm

    #1312462

    Laura Schmalzbauer (4/15/2011)

    IndexID Name

    0 NULL

    2 IX_mms_cs_linkobjectstateattribrefere

    3 IX_mms_cs_linkrefereobjectattribstate

    4 IX_mms_cs_linkobject_idis_deleted

    5 IX_mms_cs_linkobject_idis_renamed

    and the index that is giving me issues is index_id = 5

    Did you actually rename the index somewhere along the line? That can sometimes cause a problem. Instead of renaming it, drop it and recreate it.

    --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)
    Intro to Tally Tables and Functions

  • April 17, 2011 at 5:52 pm

    #1312570

    Laura Schmalzbauer (4/15/2011)

    IndexID Name

    0 NULL

    2 IX_mms_cs_linkobjectstateattribrefere

    3 IX_mms_cs_linkrefereobjectattribstate

    4 IX_mms_cs_linkobject_idis_deleted

    5 IX_mms_cs_linkobject_idis_renamed

    and the index that is giving me issues is index_id = 5

    Is that the exact output from sys.indexes, with the case of the index names intact? If it's not and you have a case sensitive collation that could explain the "Cannot find index" message.

  • April 18, 2011 at 5:37 am

    #1312691

    Todd Engen (4/17/2011)

    Laura Schmalzbauer (4/15/2011)

    IndexID Name

    0 NULL

    2 IX_mms_cs_linkobjectstateattribrefere

    3 IX_mms_cs_linkrefereobjectattribstate

    4 IX_mms_cs_linkobject_idis_deleted

    5 IX_mms_cs_linkobject_idis_renamed

    and the index that is giving me issues is index_id = 5

    Is that the exact output from sys.indexes, with the case of the index names intact? If it's not and you have a case sensitive collation that could explain the "Cannot find index" message.

    This is the exact output from sys.indexes

  • April 18, 2011 at 5:38 am

    #1312692

    Jeff Moden (4/16/2011)

    Laura Schmalzbauer (4/15/2011)

    IndexID Name

    0 NULL

    2 IX_mms_cs_linkobjectstateattribrefere

    3 IX_mms_cs_linkrefereobjectattribstate

    4 IX_mms_cs_linkobject_idis_deleted

    5 IX_mms_cs_linkobject_idis_renamed

    and the index that is giving me issues is index_id = 5

    Did you actually rename the index somewhere along the line? That can sometimes cause a problem. Instead of renaming it, drop it and recreate it.

    I have not renamed the index as I am new to the company and trying to make their servers run more efficiently.

  • April 18, 2011 at 5:40 am

    #1312695

    GilaMonster (4/16/2011)

    Is it disabled? Hypothetical? (check the other columns in sys.indexes)

    The index isn't disabled or hypothetical.

    Here are the results of the other columns from sys.indexes:

    object_id 149575571

    name IX_mms_cs_linkobject_idis_renamed

    index_id 5

    type 2

    type_desc NONCLUSTERED

    is_unique 0

    data_space_id 1

    ignore_dup_key 0

    is_primary_key 0

    is_unique_constraint 0

    fill_factor 0

    is_padded 0

    is_disabled 0

    is_hypothetical 0

    allow_row_locks 1

    allow_page_locks 1

    has_filter 0

    filter_definition NULL

Viewing 11 posts - 1 through 10 (of 10 total)

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