I have encountered the table corruption issue on one of the server. This server (SQL Server 2000 Ent edition on windows 2000) has publication database of pull-type merge replication. One of the table (MyDB.dbo.TestTABLE) got corrupted which is NOT being replicated (not a part of publication). Bad part is we do not have the good consistent backup.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 245575913, index ID 0: Page (1:26457) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 245575913, index ID 0, page (1:26457), row 0. Test (nVarCols && (hdr->r_tagA & VARIABLE_COLUMNS)) failed. Values are 0 and 32.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 245575913, index ID 1. Page (1:26457) was not seen in the scan although its parent (1:2647915) and previous (1:1778496) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 245575913, index ID 1. Page (1:713577) is missing a reference from previous page (1:26457). Possible chain linkage problem.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 245575913. The text, ntext, or image node at page (1:602493), slot 29, text ID 1083010711552 is not referenced.
DBCC results for 'TestTABLE'.
There are 3527305 rows in 68075 pages for object 'TestTABLE'.
CHECKTABLE found 0 allocation errors and 5 consistency errors in table 'TestTABLE' (object ID 245575913).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (MyDB.dbo.TestTABLE).
I have restored the database in test environment and came to know that dbcc checktable with repair_allow_data_loss is the only option to correct this problem. After repair SQL Server dropped the page (1:26457) index ID 0 ...
Next step is to correct the problem in production environment. Before I touch the production environment, I would like to know the risks and mitigation of this activity.
My concerns are:
1. Changing the database to single_user mode which is publisher in pull-type merge replication
2. Steps to be taken to disable\enable the merge replication before\after repair
Let me know if you have more inputs on this.