Can't find index even though it exists

Question

Can't find index even though it exists

Laura Schmalzbauer

SSCommitted

Points: 1672
More actions
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?

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

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

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 1

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Laura Schmalzbauer SSCommitted Points: 1672 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Laura Schmalzbauer SSCommitted Points: 1672 More actions · Answer 4

IndexID Name

0NULL

2IX_mms_cs_linkobjectstateattribrefere

3IX_mms_cs_linkrefereobjectattribstate

4IX_mms_cs_linkobject_idis_deleted

5IX_mms_cs_linkobject_idis_renamed

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

Laura Schmalzbauer (4/15/2011)
IndexID Name
0NULL
2IX_mms_cs_linkobjectstateattribrefere
3IX_mms_cs_linkrefereobjectattribstate
4IX_mms_cs_linkobject_idis_deleted
5IX_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)

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 7

Laura Schmalzbauer (4/15/2011)
IndexID Name
0NULL
2IX_mms_cs_linkobjectstateattribrefere
3IX_mms_cs_linkrefereobjectattribstate
4IX_mms_cs_linkobject_idis_deleted
5IX_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.

Laura Schmalzbauer SSCommitted Points: 1672 More actions · Answer 8

Todd Engen (4/17/2011)
Laura Schmalzbauer (4/15/2011)
IndexID Name
0NULL
2IX_mms_cs_linkobjectstateattribrefere
3IX_mms_cs_linkrefereobjectattribstate
4IX_mms_cs_linkobject_idis_deleted
5IX_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

Laura Schmalzbauer SSCommitted Points: 1672 More actions · Answer 9

Jeff Moden (4/16/2011)
Laura Schmalzbauer (4/15/2011)
IndexID Name
0NULL
2IX_mms_cs_linkobjectstateattribrefere
3IX_mms_cs_linkrefereobjectattribstate
4IX_mms_cs_linkobject_idis_deleted
5IX_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.

Laura Schmalzbauer SSCommitted Points: 1672 More actions · Answer 10

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_id149575571

nameIX_mms_cs_linkobject_idis_renamed

index_id5

type2

type_descNONCLUSTERED

is_unique0

data_space_id1

ignore_dup_key0

is_primary_key0

is_unique_constraint0

fill_factor0

is_padded0

is_disabled0

is_hypothetical0

allow_row_locks1

allow_page_locks1

has_filter0

filter_definitionNULL