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.
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 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.
IMPORTANT: 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 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.
| Target | Microsoft.SQLServer.2000.DBEngine | ||
| Category | EventCollection | ||
| Enabled | True | ||
| Event Source | $Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$ | ||
| Alert Generate | True | ||
| Alert Severity | Error | ||
| Alert Priority | Normal | ||
| Remotable | True | ||
| Alert Message |
| ||
| Event Log | Application | ||
| Comment | Mom2005ID='{1F077E02-78D9-4BAC-9D4D-8800B2046CAE}' |
| ID | Module Type | TypeId | RunAs |
|---|---|---|---|
| EventDS | DataSource | Microsoft.Windows.EventProvider | Default |
| GenerateAlert | WriteAction | System.Health.GenerateAlert | Default |
Home<Rule ID="Microsoft.SQLServer.2000.The_nonclustered_index_indicated_by_the_index_ID_is_in_error" Target="SQL2000Core!Microsoft.SQLServer.2000.DBEngine" Enabled="true" Comment="Mom2005ID='{1F077E02-78D9-4BAC-9D4D-8800B2046CAE}'" Remotable="true">
<Category>EventCollection</Category>
<DataSources>
<DataSource ID="EventDS" TypeID="Windows!Microsoft.Windows.EventProvider">
<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>
<RegExExpression>
<ValueExpression>
<XPathQuery>EventDisplayNumber</XPathQuery>
</ValueExpression>
<Operator>MatchesMOM2005RegularExpression</Operator>
<Pattern>^(17052|17055)$</Pattern>
</RegExExpression>
</Expression>
<Expression>
<RegExExpression>
<ValueExpression>
<XPathQuery>EventDescription</XPathQuery>
</ValueExpression>
<Operator>MatchesMOM2005BooleanRegularExpression</Operator>
<Pattern>((^.*644 and (not ^.*\n.*644)) or (^.*644.*\n.*644)) AND (NOT ^.*[0-9]+.*644) AND (NOT ^.*644[0-9]+)</Pattern>
</RegExExpression>
</Expression>
</And>
</Expression>
</DataSource>
</DataSources>
<WriteActions>
<WriteAction ID="GenerateAlert" TypeID="SystemHealth!System.Health.GenerateAlert">
<Priority>1</Priority>
<Severity>2</Severity>
<AlertOwner>$Data/PublisherName$</AlertOwner>
<AlertMessageId>$MPElement[Name="Microsoft.SQLServer.2000.The_nonclustered_index_indicated_by_the_index_ID_is_in_error.AlertMessage"]$</AlertMessageId>
<AlertParameters>
<AlertParameter1>$Data/EventDescription$</AlertParameter1>
</AlertParameters>
<Suppression>
<SuppressionValue/>
</Suppression>
</WriteAction>
</WriteActions>
</Rule>