January 2, 2013 at 6:18 am
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 ?
January 2, 2013 at 7:58 am
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
January 2, 2013 at 8:04 am
Don't rebuild the index. It'll likely fail. Drop the index and recreate it.
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
January 2, 2013 at 8:49 am
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
January 2, 2013 at 11:34 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2013 at 11:49 am
Thanks for help me,
I will DROP and CREATE the index tonight and re-run DBCC CHECKDB and post results here
January 2, 2013 at 12:01 pm
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
January 2, 2013 at 4:11 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2013 at 8:13 am
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
January 3, 2013 at 8:45 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2013 at 9:16 am
I've seen differing opinions from MVPs about the best way to submit BOL feedback. One said Community notes, one said Connect.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 3, 2013 at 9:32 am
Connect.
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
January 5, 2013 at 1:01 pm
https://connect.microsoft.com/SQLServer/feedback/details/775884
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply