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

How to fix corrupted index on subscriber table Expand / Collapse
Author
Message
Posted Thursday, August 4, 2005 2:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 26, 2008 1:22 PM
Points: 9, Visits: 2

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

Post #207648
Posted Friday, August 5, 2005 1:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 1, 2007 8:58 PM
Points: 96, Visits: 1

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

Post #207768
Posted Friday, August 5, 2005 4:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:32 AM
Points: 278, Visits: 229

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

 

Post #207797
Posted Friday, August 5, 2005 7:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 26, 2008 1:22 PM
Points: 9, Visits: 2

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

 

Post #207862
Posted Monday, August 8, 2005 5:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 26, 2008 1:22 PM
Points: 9, Visits: 2

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

 

Post #208517
Posted Thursday, August 12, 2010 2:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 9:40 AM
Points: 59, Visits: 137
Thanks.. dbcc dbreindex worked for me...
Post #968552
Posted Wednesday, March 2, 2011 12:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 31, 2014 7:20 AM
Points: 2, Visits: 71
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!
Post #1072149
Posted Wednesday, March 2, 2011 12:56 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 @ 1:43 AM
Points: 40,617, Visits: 37,083
Please note: 6 year old thread


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 #1072152
Posted Friday, July 13, 2012 11:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 3:38 PM
Points: 194, Visits: 331
GilaMonster (3/2/2011)
Please note: 6 year old thread


7 years now, and still relevant.



-Ken
Post #1329603
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse