Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DBCC_CheckDB Error in Index Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 6:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 3:32 AM
Points: 2, Visits: 7
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 ?
Post #1401804
Posted Wednesday, January 2, 2013 7:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:04 PM
Points: 7,141, Visits: 12,768
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 ?

Rebuild the index named IN_DOC_DTREAL on the doc table and then re-run

DBCC CHECKDB(N'SLine2') WITH NO_INFOMSGS


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1401856
Posted Wednesday, January 2, 2013 8:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 40,663, Visits: 37,128
Don't rebuild the index. It'll likely fail. Drop the index and recreate it.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1401862
Posted Wednesday, January 2, 2013 8:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:04 PM
Points: 7,141, Visits: 12,768
Reformatting the Agent job output for easier reading:

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).

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. Happy to learn.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1401878
Posted Wednesday, January 2, 2013 11:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 40,663, Visits: 37,128
opc.three (1/2/2013)
Are you thinking of reorganize?


No.

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.


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/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1401999
Posted Wednesday, January 2, 2013 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 3:32 AM
Points: 2, Visits: 7
Thanks for help me,

I will DROP and CREATE the index tonight and re-run DBCC CHECKDB and post results here
Post #1402013
Posted Wednesday, January 2, 2013 12:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:04 PM
Points: 7,141, Visits: 12,768
GilaMonster (1/2/2013)
opc.three (1/2/2013)
Are you thinking of reorganize?


No.

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.


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/

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:

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).


I'll have a read through the link you sent from Paul.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1402018
Posted Wednesday, January 2, 2013 4:11 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 40,663, Visits: 37,128
opc.three (1/2/2013)
GilaMonster (1/2/2013)
opc.three (1/2/2013)
Are you thinking of reorganize?


No.

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.


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/

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:

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).


Books online is wrong. Again. From the link I posted, emphasis mine

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).





Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1402116
Posted Thursday, January 3, 2013 8:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:04 PM
Points: 7,141, Visits: 12,768
You continue to amaze. Thanks for the info, Gail!

As an aside, I added a Community note to the 2008, 2008 R2 and 2012 Books Online pages. Hopefully that's the correct channel to submit feedback and the articles will be updated.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1402406
Posted Thursday, January 3, 2013 8:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 40,663, Visits: 37,128
Well, probably less that it's wrong and more that the page in question was changed from reading "In SQL Server 2005" to "In SQL Server 2008" with noone on the docs team knowing that anything more had changed than that.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1402419
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse