Post reply

How to fix corrupted index on subscriber table

  • August 4, 2005 at 2:34 pm

    #88421

    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

  • August 5, 2005 at 1:55 am

    #579925

    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,

    Don't count what you do, do what counts.

    SQL Draggon

  • August 5, 2005 at 4:55 am

    #579948

    I've seen this problem somewhat frequently. The solution is to do a DBCC CHECKALLOC

        ( 'database_name' ,  REPAIR_ALLOW_DATA_LOSS )    

    --

    Hilary Cotter

    Looking for a SQL Server replication book?

    http://www.nwsu.com/0974973602.html

    Looking for a FAQ on Indexing Services/SQL FTS

    http://www.indexserverfaq.com

     

  • August 5, 2005 at 7:57 am

    #580004

    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 1

    Table 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 1

    Table 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 1

    Table 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 1

    Too 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 1

    Table 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 1

    Index 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 1

    Table 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 1

    Index 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 1

    Table 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 1

    Data 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 1

    Table 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 1

    Index 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

     

  • August 8, 2005 at 5:06 pm

    #580559

    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 1

    Table 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 1

    Data 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 1

    Table 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

     

  • August 12, 2010 at 2:17 pm

    #1206318

    Thanks.. dbcc dbreindex worked for me...

  • March 2, 2011 at 12:52 pm

    #1293700

    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!

  • March 2, 2011 at 12:56 pm

    #1293703

    Please note: 6 year old thread

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 13, 2012 at 11:04 am

    #1512660

    GilaMonster (3/2/2011)

    Please note: 6 year old thread

    7 years now, and still relevant.


    -Ken

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply