[Deprecated] The nonclustered index indicated by the index ID is in error

Microsoft.SQLServer.2008.The_nonclustered_index_indicated_by_the_index_ID_is_in_error_1_5_Rule (Rule)

Knowledge Base article:

Summary

This error occurs when the nonclustered index indicated by the index ID is in error. The corruption is detected when a process tries to delete a nonexistent row.

Resolutions

Execute DBCC CHECKDB without a repair clause to determine the extent of the damage. Then, execute DBCC CHECKDB with REPAIR_REBUILD clause to correct the damage. If the problems persist, either drop and re-create the index (as shown below) or contact your primary support provider.

Important : If executing DBCC CHECKDB with the REPAIR_REBUILD clause does not correct the index problem, or if you are unsure what effect DBCC CHECKDB with the REPAIR_REBUILD clause has on your data, contact your primary support provider.

To drop and re-create the index:

Record the value of index page and index ID specified in the error text.

Identify, which table and index correspond to the index page number.

Note the object ID.

If the object with the error is a system table (the object ID is less than 100), you cannot drop the index. Execute DBCC CHECKDB with the REPAIR_REBUILD clause or restore the database from a known clean backup.

If the object ID is greater than 100, drop and re-create the index using the table name and index name obtained in Step 1. In most cases, this will clear the error.

Execute DBCC CHECKDB with the REPAIR_REBUILD clause on the affected database to verify that all problems have been resolved.

If the problems persist, this procedure might not be sufficient to clean up the index error. In this case, contact your primary support provider. Have the output from DBCC CHECKDB available for review.

Overrideable Parameters

Name	Description	Default Value
Enabled	Enables or disables the workflow.	No
Priority	Defines Alert Priority.	1
Severity	Defines Alert Severity.	2

Element properties:

Target

Microsoft.SQLServer.2008.DBEngine

Category

EventCollection

Enabled

False

Event_ID

644

Event Source

$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$

Alert Generate

True

Alert Severity

Error

Alert Priority

Normal

Remotable

True

Alert Message

The nonclustered index indicated by the index ID is in error

{0}

Event Log

Application

Comment

Mom2008ID='{C4E8CBD6-D28E-4F67-B4C3-5B543CE8CACF}';MOM2008GroupID={467ECC75-C5DA-42BD-955C-A73BBB51AF74}

Member Modules:

ID	Module Type	TypeId	RunAs
_F6DA1507_12AF_11D3_AB21_00A0C98620CE_	DataSource	Microsoft.Windows.EventProvider	Microsoft.SQLServer.SQLDefaultAccount
GenerateAlert	WriteAction	System.Health.GenerateAlert	Default

Source Code:

<Rule ID="Microsoft.SQLServer.2008.The_nonclustered_index_indicated_by_the_index_ID_is_in_error_1_5_Rule" Target="SQL2008Core!Microsoft.SQLServer.2008.DBEngine" Enabled="false" ConfirmDelivery="true" Remotable="true" Comment="Mom2008ID='{C4E8CBD6-D28E-4F67-B4C3-5B543CE8CACF}';MOM2008GroupID={467ECC75-C5DA-42BD-955C-A73BBB51AF74}">

  <Category>EventCollection</Category>

  <DataSources>

    <DataSource ID="_F6DA1507_12AF_11D3_AB21_00A0C98620CE_" Comment="{F6DA1507-12AF-11D3-AB21-00A0C98620CE}" TypeID="Windows!Microsoft.Windows.EventProvider" RunAs="SQL!Microsoft.SQLServer.SQLDefaultAccount">

      <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>

      <LogName>Application</LogName>

      <Expression>

        <And>

          <Expression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQuery>PublisherName</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

                <Value>$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$</Value>

              </ValueExpression>

            </SimpleExpression>

          </Expression>

          <Expression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQuery>EventDisplayNumber</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

                <Value>644</Value>

              </ValueExpression>

            </SimpleExpression>

          </Expression>

        </And>

      </Expression>

    </DataSource>

  </DataSources>

  <WriteActions>

    <WriteAction ID="GenerateAlert" TypeID="SystemHealth!System.Health.GenerateAlert">

      <Priority>1</Priority>

      <Severity>2</Severity>

      <AlertMessageId>$MPElement[Name="Microsoft.SQLServer.2008.The_nonclustered_index_indicated_by_the_index_ID_is_in_error_1_5_Rule.AlertMessage"]$</AlertMessageId>

      <AlertParameters>

        <AlertParameter1>Event ID: $Data/EventDisplayNumber$. $Data/EventDescription$</AlertParameter1>

      </AlertParameters>

      <Suppression>

        <SuppressionValue/>

      </Suppression>

    </WriteAction>

  </WriteActions>

</Rule>