Sql-server – Found errors in table after executing DBCC CHECKTABLE command

sql serversql-server-2008

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.

Best Answer

Realizing this situation is long over I'm still going to put in my 2 cents for anyone else finding themselves in a similar situation.

  1. You have a fair amount of corruption in that table. Run a DBCC CHECKDB on the database ASAP.
  2. I hope you have a good & recent backup. That's going to be your best bet for recovery.

  3. If you don't have a recent backup then you have a number of lesser possibilities.

    • Use the dreaded repair_allow_data_loss. This is your LAST option for a reason. It can and probably will cause you to lose some data. And by some it could be one column of one row all the way up to .. well .. all of it. And you won't have any idea how much was lost.
    • Create a new database with the same structures and use something like SSIS to copy the data across. Still probably going to lose data here.

Regardless, if you have the time I would look over the answers given in Steve Stedman's Database Corruption Challenge. He posted corrupt databases and various people posted their solutions to fixing them. These solutions range from the normal (as above) to some interesting and unusual solutions. One or more of them may work for you and let you salvage your database without data loss.

Also, and this is important too, if you have a corrupted database it's probably corrupt for a reason. Check your IO substructure etc to make sure there isn't a problem there. And also check the other databases on that instance with CHECKDB to make sure you don't have additional problems.

Last but not least. You should be running regular backups, testing them, and running regular CHECKDBs on all databases for situations exactly like this. The sooner you understand you have a problem the better chance of fixing it without loss. Not to mention having good, recoverable backups just in case.

Related Question