SQLServerCentral / SQL Server 7,2000 / Replication / How to fix corrupted index on subscriber table / Latest Posts

RE: How to fix corrupted index on subscriber table

spongemagnet — Fri, 13 Jul 2012 11:04:06 GMT

[quote][b]GilaMonster (3/2/2011)[/b][hr]Please note: 6 year old thread[/quote]7 years now, and still relevant.

RE: How to fix corrupted index on subscriber table

GilaMonster — Wed, 02 Mar 2011 12:56:21 GMT

Please note: 6 year old thread

RE: How to fix corrupted index on subscriber table

matthew319 — Wed, 02 Mar 2011 12:52:05 GMT

Using the DBCC DBREINDEX in a cursor that handles all tables of whatever database resolved the issue for me. Thanks for the information! This saved me from having to push a very large database over a very slow connection!

RE: How to fix corrupted index on subscriber table

bmannar — Thu, 12 Aug 2010 14:17:54 GMT

Thanks.. dbcc dbreindex worked for me...

RE: How to fix corrupted index on subscriber table

sanny bae — Mon, 08 Aug 2005 17:06:00 GMT

FYI, here's what I did:

DBCC DBREINDEX was sufficient to fix the error like the following:

Server: Msg 8951, Level 16, State 1, Line 1Table error: Table 'MSmerge_contents' (ID 1957582012). Missing or invalid key in index 'nc4MSmerge_contents' (ID 4) for the row:Server: Msg 8955, Level 16, State 1, Line 1Data row (1:20154:9) identified by (RID = (1:20154:9) tablenick = 2236004 and rowguid = 55EA2ACD-1475-4F0C-953B-E76A2CBAC33BØ™¨) has index values (rowguid = 55EA2ACD-1475-4F0C-953B-E76A2CBAC33B`¢¨ and tablenick = 2236004 and rowguid = 55EA2ACD-1475-4F0C-953B-E76A2CBAC33B`¢¨).

However, to fix error like the following, a DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS was required:

Server: Msg 8964, Level 16, State 1, Line 1Table error: Object ID 658101385. The text, ntext, or image node at page (3:407121), slot 4, text ID 145881235456 is not referenced.

In fact, after I did the DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS, and I ran the checkdb again, one index of one table still had error. I had to redo the DBCC DBREINDEX on that particular index and that fixed the problem.

baes

RE: How to fix corrupted index on subscriber table

sanny bae — Fri, 05 Aug 2005 07:57:00 GMT

Thank you for your postings.

Rookie, I believe DBCC INDEXDEFRAG shuffles and reorganizes the existing pages that the index occupies, while DBCC DBREINDEX rebuild the index table itself.

Hillary, DBCC CHECKALLOC doesn't return any errors. DBCC CHECKDB returns the following:

...Server: Msg 8964, Level 16, State 1, Line 1Table error: Object ID 658101385. The text, ntext, or image node at page (3:407121), slot 4, text ID 145881235456 is not referenced.Server: Msg 8964, Level 16, State 1, Line 1Table error: Object ID 658101385. The text, ntext, or image node at page (3:407601), slot 12, text ID 146103599104 is not referenced.Server: Msg 8964, Level 16, State 1, Line 1Table error: Object ID 658101385. The text, ntext, or image node at page (3:408496), slot 10, text ID 146519752704 is not referenced.Server: Msg 8986, Level 16, State 1, Line 1Too many errors found (201) for object ID 658101385. To see all error messages rerun the statement using "WITH ALL_ERRORMSGS".Server: Msg 8952, Level 16, State 1, Line 1Table error: Database 'custTrans', index 'MSmerge_contents.nc2MSmerge_contents' (ID 1957582012) (index ID 2). Extra or invalid key for the keys:Server: Msg 8956, Level 16, State 1, Line 1Index row (3:4341892:27) with values (generation = 672333 and tablenick = 2236004 and rowguid = 93D0F4E5-3290-4372-9799-4BB3317E4167`¢¨) points to the data row identified by ().Server: Msg 8952, Level 16, State 1, Line 1Table error: Database 'custTrans', index 'MSmerge_contents.nc3MSmerge_contents' (ID 1957582012) (index ID 3). Extra or invalid key for the keys:Server: Msg 8956, Level 16, State 1, Line 1Index row (3:869208:101) with values (partchangegen = NULL and tablenick = 2236004 and rowguid = 93D0F4E5-3290-4372-9799-4BB3317E4167`¢¨) points to the data row identified by ().Server: Msg 8951, Level 16, State 1, Line 1Table error: Table 'MSmerge_contents' (ID 1957582012). Missing or invalid key in index 'nc4MSmerge_contents' (ID 4) for the row:Server: Msg 8955, Level 16, State 1, Line 1Data row (1:20154:9) identified by (RID = (1:20154:9) tablenick = 2236004 and rowguid = 55EA2ACD-1475-4F0C-953B-E76A2CBAC33BØ™¨) has index values (rowguid = 55EA2ACD-1475-4F0C-953B-E76A2CBAC33B`¢¨ and tablenick = 2236004 and rowguid = 55EA2ACD-1475-4F0C-953B-E76A2CBAC33B`¢¨).Server: Msg 8952, Level 16, State 1, Line 1Table error: Database 'custTrans', index 'MSmerge_contents.nc4MSmerge_contents' (ID 1957582012) (index ID 4). Extra or invalid key for the keys:Server: Msg 8956, Level 16, State 1, Line 1Index row (1:107517:32) with values (rowguid = 93D0F4E5-3290-4372-9799-4BB3317E4167`¢¨ and tablenick = 2236004 and rowguid = 93D0F4E5-3290-4372-9799-4BB3317E4167`¢¨) points to the data row identified by ().There are 4988667 rows in 92988 pages for object 'detTrans'.CHECKDB found 0 allocation errors and 244 consistency errors in table 'detTrans' (object ID 658101385).

DBCC results for 'MSmerge_contents'.There are 2978547 rows in 309775 pages for object 'MSmerge_contents'.CHECKDB found 0 allocation errors and 4 consistency errors in table 'MSmerge_contents' (object ID 1957582012).

So I was wondering if DBCC DBREINDEX is sufficient to fix the problem, or do I have to run a DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS?

Thanks,

baes

RE: How to fix corrupted index on subscriber table

Hilary Cotter — Fri, 05 Aug 2005 04:55:00 GMT

I've seen this problem somewhat frequently. The solution is to do a DBCC CHECKALLOC ( 'database_name' , REPAIR_ALLOW_DATA_LOSS )

-- Hilary CotterLooking for a SQL Server replication book?http://www.nwsu.com/0974973602.htmlLooking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com

RE: How to fix corrupted index on subscriber table

SQL Draggon — Fri, 05 Aug 2005 01:55:00 GMT

why don't you try a DBCC IndexDefrag insted of a dbcc dbreindex. it can be done without taking the db or table off-line.

I would shutdown the replication agent.

Run DBCC IndexDefrag <check BOL for the exact syntax >

Re-start the replication agent.

good luck,

How to fix corrupted index on subscriber table

sanny bae — Thu, 04 Aug 2005 14:34:00 GMT

Hi there,

My company uses MS Merge Replication in replicating data across different cities.

Here's the merge replication model:Site A: (merge replication) - 4 servers - A1, A2, A3, A4 - A1 is the publisher - A2, A3, A4 subscribe to A1

Site B: (merge replication) - 3 servers - B1, B2, B3 - B1 is the publisher for site B. - B1 also subscribes to A1, in order to get Site A's data. - B2 and B3 subscribe to B1

All servers on Site A and Site B generate data, and the data generated needs to be replicated all over to the other servers.

An index of a user table on A2 (first subscriber) is corrupted. The database size is 20GB, and this corrupted table has over 5 million rows.

The system is live and we can't afford to lose any data. We also don't have a test system in place where we could test. This is what I have in mind, can you tell me if this is sufficient to fix the problem?

- Shut down merge agent and SQL server on A2.- Disable merge agent on A1 so it won't write to A2.- Start up SQL server alone on A2.- Make it single user mode for this database.- DBCC DBREINDEX on corrupted table.- Remove single user mode.- Enable merge agent on A1.- Restart SQL server on A2.

Please help.

Thanks in advance,baes