I am trying to alter table to add primary key
ALTER TABLE GeoCity
ADD CONSTRAINT PK_GeoCity
PRIMARY KEY(locId)
and got this error message
Msg 1750, Level 16, State 0, Line 1
then I run this command to check table
DBCC CHECKTABLE('GeoCity');
with this I found these errors
DBCC results for 'GeoCity'. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12307), slot 52 should be pointed to by forwarding row page (1:38514), slot 78. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12307), slot 53 should be pointed to by forwarding row page (1:38514), slot 83. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12310), slot 56 should be pointed to by forwarding row page (1:38514), slot 0. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12310), slot 57 should be pointed to by forwarding row page (1:38514), slot 48. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12310), slot 58 should be pointed to by forwarding row page (1:38514), slot 86. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12319), slot 40 should be pointed to by forwarding row page (1:38514), slot 26. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12390), slot 48 should be pointed to by forwarding row page (1:38514), slot 1. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12390), slot 49 should be pointed to by forwarding row page (1:38514), slot 28. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12390), slot 50 should be pointed to by forwarding row page (1:38514), slot 39. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12703), slot 28 should be pointed to by forwarding row page (1:38514), slot 43. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12852), slot 7 should be pointed to by forwarding row page (1:38514), slot 12. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12852), slot 8 should be pointed to by forwarding row page (1:38514), slot 73. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12884), slot 56 should be pointed to by forwarding row page (1:38514), slot 53. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12928), slot 41 should be pointed to by forwarding row page (1:38514), slot 97. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12957), slot 48 should be pointed to by forwarding row page (1:38514), slot 23. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:12993), slot 60 should be pointed to by forwarding row page (1:38514), slot 59. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:13034), slot 38 should be pointed to by forwarding row page (1:38514), slot 49. Did not encounter forwarding row. Possible allocation error. Msg 8928, Level 16, State 1, Line 1 Object ID 241435934, index ID 0, partition ID 72057594043236352, alloc unit ID 72057594046644224 (type In-row data): Page (1:38514) could not be processed. See other errors for details. Msg 8944, Level 16, State 18, Line 1 Table error: Object ID 241435934, index ID 0, partition ID 72057594043236352, alloc unit ID 72057594046644224 (type In-row data), page (1:38514), row 84. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 20 and 52. Msg 8944, Level 16, State 18, Line 1 Table error: Object ID 241435934, index ID 0, partition ID 72057594043236352, alloc unit ID 72057594046644224 (type In-row data), page (1:38514), row 84. Test (columnOffsets->offTbl [varColumnNumber]
= priorOffset) failed. Values are 20 and 52. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:39411), slot 48 should be pointed to by forwarding row page (1:38514), slot 21. Did not encounter forwarding row. Possible allocation error. Msg 8994, Level 16, State 1, Line 1 Object ID 241435934, forwarded row page (1:39411), slot 49 should be pointed to by forwarding row page (1:38514), slot 70. Did not encounter forwarding row. Possible allocation error. There are 311823 rows in 4350 pages for object "GeoCity". CHECKTABLE found 0 allocation errors and 22 consistency errors in table 'GeoCity' (object ID 241435934). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (XYZ.dbo.GeoCity). DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I couldn't understand these messages help me to find out solution.
DBCC CHECKDBon it -- does it come back clean? Dropping and recreating indexes on this table won't help in this case -- the problem is that the forwarding and forwarded pointers in some of the records in the base table appear to be corrupt. – Jon Seigel Apr 29 '12 at 1:37