Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from stackoverflow.com Mar 2 '12 at 14:08

This question came from our site for professional and enthusiast programmers.

    
Are you able to drop your indexes and run your DBCC again? – Eric Higgins Mar 2 '12 at 16:47
1  
If you have the "before" state, run DBCC CHECKDB on 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
    
This is a standard corruption problem. There are great resources on the web explaining what to do. – usr May 9 '15 at 20:03

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.

share|improve this answer

You've got corruption and DBCC CHECKDB is recommending you run

DBCC CHECKDB (Database, REPAIR_ALLOW_DATA_LOSS) 

as noted on the third line from the bottom of your error text. That being said, you may want to try REPAIR_REBUILD first since it's less intrusive than REPAIR_ALLOW_DATA_LOSS--for which you may lose data.

'...ID 241435934). repair_allow_data_loss is the minimum repair level..."

Here's a link to another recommendation from Microsoft to restore from backup if the repair fails.

Here's a link to more details on the repair syntax of DBCC CHECKDB

share|improve this answer
2  
Please do not ever recommend REPAIR_ALLOW_DATA_LOSS. It is always, without question, the absolute last resort. – Kris Gruttemeyer May 9 '15 at 17:52
    
"CHECKDB is recommending" certainly not recommended by this command. This is a last resort option because, surprise, it can lose data. – usr May 9 '15 at 20:02
    
I'd agree, but you need to contact Microsoft. It's embedded in the actual text of their error messages: object ID 241435934). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (XYZ.dbo.GeoCity). Thus, "CHECKDB Recommends", not "I recommend".... – Sting Jun 3 '15 at 16:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.