I am having a problem with replicating a table, and I finally tracked it down to a consistency error on the source table, but I am stumped as to what is causing the consistency error.
I ran DBCC Checkdb and found the following error on the table
Msg 2570, Level 16, State 3, Line 1 Page (1:5217772), slot 16 in object ID 1038014829, index ID 1, partition ID 349502316544000, alloc unit ID 349502316544000 (type "In-row data"). Column "MHGPTX" value is out of range for data type "varchar". Update column to a legal value.
I used DBCC PAGE to what row within the table was causing the problem, and tracked down the offending row.
Slot 16 Column 6 Offset 0x28 Length 150
MHGPTX = [Error converting to string (length 150 bytes)]
This is where I got stuck, the data in the field looks fine, the data is well under the max size for the column, I can query the row, etc.
I found a few references online to similiar problems, where the problem was resolved by updating the row using the same data, so I tried that, but with no change.
Any advice would be greatly appreciated
-
Can you share column definition (with collation)?user_0– user_02015年08月07日 15:13:04 +00:00Commented Aug 7, 2015 at 15:13
-
The collation is Chinese_PRC_CI_AS The column is varchar(1500), nullPatrick– Patrick2015年08月07日 15:14:15 +00:00Commented Aug 7, 2015 at 15:14
-
Looks like a checkdb bug. Delete everything in that database except for that row. Make sure CHECKDB still reports the error. Then, report the bug and attach the MDF and LDF privately on MS Connect.usr– usr2015年08月08日 22:04:21 +00:00Commented Aug 8, 2015 at 22:04
-
This is in a production database. So deleting content is a no go.Patrick– Patrick2015年08月10日 13:27:20 +00:00Commented Aug 10, 2015 at 13:27
-
Just an update. I have a case open with microsoft, no solution yet, or root cause yet, but still working on it. Once I get a solution and or root cause from MS I will update this and give the details.Patrick– Patrick2015年10月22日 18:02:37 +00:00Commented Oct 22, 2015 at 18:02
2 Answers 2
I would try that:
- drop all indexes
- rebuild the clustered index
- run dbcc
- create index
- run dbcc
I would also check the hardware (disk) for inconsistencies.
-
That sounds pretty good and I tried to sell the boss on letting me make the changes in prod, but he wants me to open a case with microsoft before going down that road. Opened a case today and I will see what they say and post the info here if I get a resolution.Patrick– Patrick2015年08月10日 13:28:58 +00:00Commented Aug 10, 2015 at 13:28
-
if you have an enterprise version, you can also rebuild it online.Julien Vavasseur– Julien Vavasseur2015年08月10日 15:23:35 +00:00Commented Aug 10, 2015 at 15:23
Its been awhile, but I realized I never updated this.
I found that the error was caused by double byte characters (asian language), and the second byte being truncated. I was able to correct the problem by increasing the column with by one, and then changing it back to the original width. This truncated the lingering first byte of the double byte character, and checkdb now returns without error.