mvargas109 (1/2/2013)

---

Executed as user: LAVOISIER\SQLAGENTACCOUNT. ...600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 2:00:00 PM Progress: 2012-12-30 14:00:00.68 Source: {4090B986-66AE-4E33-9706-E39A5B76F0D3} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2012-12-30 14:00:01.52
Source: Check Database Integrity Executing query "USE [SLine2] ".: 50% complete End Progress Error: 2012-12-30 14:06:14.63 Code: 0xC002F210 Source: Check Database Integrity Execute SQL Task
Description: Executing the query "DBCC CHECKDB(N'SLine2') WITH NO_INFOMSGS " failed with the following error: "Table error: table 'doc' (ID 142623551). Data row does not have a matching index row in the index 'IN_DOC_DTREAL' (ID 11). Possible missing or invalid keys for the index row matching: Data row (1:1607299:17) identified by (id_doc = 77728405.) with index values 'cd_cliente = 'HFA ' and dt_doc_real = '2012-10-29 08:25:00.000' and id_doc = 77728405.'. Table error: table 'doc' (ID 142623551). Index row in index 'IN_DOC_DTREAL' (ID 11) does not match any data row. Possible extra or invalid keys for: Index row (1:1608079:40) with values (cd_cliente = 'HFA ' and dt_doc_real = '2012-10-29 09:36:00.000' and id_doc = 77728405.) pointing to the data row identified by (id_doc = 77728405.). CHECKDB found 0 allocation errors and 2 consistency errors in table 'doc' (object ID 142623551). CHECKDB found 0 allocation errors and 2 consistency errors in database 'SLine2'. repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (SLine2).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error

The id_doc is unique

How to solve this error ?

Executing the query "DBCC CHECKDB(N'SLine2') WITH NO_INFOMSGS " failed with the following error:

Table error: table 'doc' (ID 142623551). Data row does not have a matching index row in the index 'IN_DOC_DTREAL' (ID 11). Possible missing or invalid keys for the index row matching: Data row (1:1607299:17) identified by (id_doc = 77728405.) with index values 'cd_cliente = 'HFA ' and dt_doc_real = '2012-10-29 08:25:00.000' and id_doc = 77728405.'.
Table error: table 'doc' (ID 142623551). Index row in index 'IN_DOC_DTREAL' (ID 11) does not match any data row. Possible extra or invalid keys for: Index row (1:1608079:40) with values (cd_cliente = 'HFA ' and dt_doc_real = '2012-10-29 09:36:00.000' and id_doc = 77728405.) pointing to the data row identified by (id_doc = 77728405.).

CHECKDB found 0 allocation errors and 2 consistency errors in table 'doc' (object ID 142623551).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'SLine2'.

repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (SLine2).

opc.three (1/2/2013)

---

Are you thinking of reorganize?

I thought a rebuild would re-scan the clustered same as a drop+recreate which would work since all inconsistencies were found in the nonclustered index.

GilaMonster (1/2/2013)

---

No.



Nope. A rebuild of a nonclustered index can read the old index to build the new (there are a number of options available). Far more efficient than reading the table (all the columns for the new index are present in the old one, and in the correct logical order), but it means that if there's corruption in the index rebuilding it will likely fail (or worse, preserve the corruption in the new index).

http://www.sqlskills.com/blogs/paul/teched-demo-nonclustered-index-corruption/

Rebuilding Indexes
Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. FOREIGN KEY constraints do not have to be dropped in advance. When indexes with 128 extents or more are rebuilt, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. For more information, see Dropping and Rebuilding Large Objects.

In earlier versions of SQL Server, you could sometimes rebuild a nonclustered index to correct inconsistencies caused by hardware failures. In SQL Server 2008, you may still be able to repair such inconsistencies between the index and the clustered index by rebuilding a nonclustered index offline. However, you cannot repair nonclustered index inconsistencies by rebuilding the index online, because the online rebuild mechanism will use the existing nonclustered index as the basis for the rebuild and thus persist the inconsistency. Rebuilding the index offline, by contrast, will force a scan of the clustered index (or heap) and so remove the inconsistency. As with earlier versions, we recommend recovering from inconsistencies by restoring the affected data from a backup; however, you may be able to repair the index inconsistencies by rebuilding the nonclustered index offline. For more information, see DBCC CHECKDB (Transact-SQL).

opc.three (1/2/2013)

---

Thanks. Books Online may need a kick, or maybe I am misreading, but I think ONLINE rebuilds read the old index, offline rescans the clustered:

Online index rebuild reads the old index to build the new one so the new index has the same missing rows as the old one. We need to do an offline index rebuild – with lines 110-115. After the last DBCC CHECKDB, the index is fixed up. Now, on SQL Server 2008, you may or may not get a query plan for the index rebuild that doesn't use the old index, because the query optimizer has some more plan choices available to it - so on SQL Server 2008 you may need to do an actual drop and create of the broken index (carefully, if its enforcing a constraint).