Integrity check failure 1

[jaylou]

Hi All,
I am driving myself crazy trying to debug this one. My Integrity check keeps failing due to:
Test (m_freeCnt == freeCnt) failed. Values are 7798 and 8094.
And
Test (emptySlotCnt == 0) failed. Values are 1 and 0.

I have been researching this all morning. I ran a simple select on the offending table and it turned out there where 2 columns that had a date that went beyond the year 9999. I have nulled out the offending columns since these dates are meaningless in the table. I can now run a select on this table and return all rows, but the integrity check still fails for the same reasons.

Can anyone please tell me how to fix this error? restoring the DB is out of the question since i dont have a clean one without this issue. I am new to this company and this job has been failing for long before I got here.

TIA,
Joe

 

[ptheriault]

look up dbcc checkable with repair option from BOL.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jaylou]

Thank for the info...
Now all I need to do is find the time when I can place the DB in single user mode.

Thanks agaon.
joe

 

[ptheriault]

you don't need to put the db in single user mode to run checkdb or checktable. here is a word of caution from Microsoft on using repair options.

Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup. Repair operations do not consider any of the constraints that may exist on or between tables. If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. If you must use REPAIR, run DBCC CHECKDB to find the repair level to use. If you are going to use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jaylou]

Unfortunatly I think I do need to run the repair. I dont have a good backup for this database without the table in the current condition. Do you think it would help if I drop and recreate this table and re-load the data?

Thanks again


When I ran the DBCC chectable, I receive the following:

DBCC results for 'EX_MESS'.
Msg 8928, Level 16, State 1, Line 1
Object ID 373576369, index ID 0: Page (1:105752) could not be processed. See other errors for details.
Msg 8939, Level 16, State 106, Line 1
Table error: Object ID 373576369, index ID 0, page (1:105752). Test (m_freeCnt == freeCnt) failed. Values are 7798 and 8094.
Msg 8939, Level 16, State 108, Line 1
Table error: Object ID 373576369, index ID 0, page (1:105752). Test (emptySlotCnt == 0) failed. Values are 1 and 0.
There are 19175 rows in 1759 pages for object 'EX_MESS'.
CHECKTABLE found 0 allocation errors and 3 consistency errors in table 'EX_MESS' (object ID 373576369).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (PA282.dbo.EX_MESS ).

 

[ptheriault]

I would make a backup of the table like this.

SELECT * INTO EX_MESS_BAK FROM EX_MESS

Then run repair with allow data loss.

Then you should run DBCC CHECKCONTRAINTS
and compare the data in your bak table to make sure you didn't loose any data.

- Paul
- If at first you don't succeed, find out if the loser gets anything.