InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 11:06:31 GMT20http://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspx[u][url=https://connect.microsoft.com/SQLServer/feedback/details/775884]https://connect.microsoft.com/SQLServer/feedback/details/775884[/url][/u]Sat, 05 Jan 2013 13:01:46 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspxConnect.Thu, 03 Jan 2013 09:32:20 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspxI've seen differing opinions from MVPs about the best way to submit BOL feedback. One said Community notes, one said Connect.Thu, 03 Jan 2013 09:16:44 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspxWell, 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.Thu, 03 Jan 2013 08:45:21 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspxYou 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.Thu, 03 Jan 2013 08:13:03 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspx[quote][b]opc.three (1/2/2013)[/b][hr][quote][b]GilaMonster (1/2/2013)[/b][hr][quote][b]opc.three (1/2/2013)[/b][hr]Are you thinking of reorganize? [/quote]No.[quote]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.[/quote]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).[url]http://www.sqlskills.com/blogs/paul/teched-demo-nonclustered-index-corruption/[/url][/quote]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:[quote][b]Rebuilding Indexes[/b]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. [b][i]Rebuilding the index offline, by contrast, will force a scan of the clustered index (or heap) and so remove the inconsistency.[/i][/b] 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).[/quote][/quote]Books online is wrong. Again. From the link I posted, emphasis mine[quote]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. [b]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[/b] (carefully, if its enforcing a constraint). [/quote]Wed, 02 Jan 2013 16:11:38 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspx[quote][b]GilaMonster (1/2/2013)[/b][hr][quote][b]opc.three (1/2/2013)[/b][hr]Are you thinking of reorganize? [/quote]No.[quote]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.[/quote]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).[url]http://www.sqlskills.com/blogs/paul/teched-demo-nonclustered-index-corruption/[/url][/quote]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:[quote][b]Rebuilding Indexes[/b]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. [b][i]Rebuilding the index offline, by contrast, will force a scan of the clustered index (or heap) and so remove the inconsistency.[/i][/b] 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).[/quote]I'll have a read through the link you sent from Paul.Wed, 02 Jan 2013 12:01:36 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspxThanks for help me, I will DROP and CREATE the index tonight and re-run DBCC CHECKDB and post results hereWed, 02 Jan 2013 11:49:51 GMTmvargas109http://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspx[quote][b]opc.three (1/2/2013)[/b][hr]Are you thinking of reorganize? [/quote]No.[quote]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.[/quote]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).[url]http://www.sqlskills.com/blogs/paul/teched-demo-nonclustered-index-corruption/[/url]Wed, 02 Jan 2013 11:34:50 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspxReformatting the Agent job output for easier reading:[quote]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).[/quote]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.Wed, 02 Jan 2013 08:49:06 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspxDon't rebuild the index. It'll likely fail. Drop the index and recreate it.Wed, 02 Jan 2013 08:04:46 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspx[quote][b]mvargas109 (1/2/2013)[/b][hr]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 ErrorThe id_doc is unique How to solve this error ?[/quote]Rebuild the index named IN_DOC_DTREAL on the doc table and then re-runDBCC CHECKDB(N'SLine2') WITH NO_INFOMSGSWed, 02 Jan 2013 07:58:58 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1401804-2893-1.aspxExecuted 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 ErrorThe id_doc is unique How to solve this error ?Wed, 02 Jan 2013 06:18:54 GMTmvargas109