DBCC CHECKDB didn't repair the errors.

Question

DBCC CHECKDB didn't repair the errors.

Nalini Devarakonda

SSChasing Mays

Points: 636
More actions
July 23, 2008 at 12:50 am

#210266

Hi,
the DBCC CHECKDB found 3 consistency errors.
though i ran REPAIR_ALLOW_DATA_LOSS clause, the errors are still not repaired.
Any suggestions???

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Got a backup?

There are cases where CheckDB cannot repair. They're not common, but they do happen. If you run into that your oprions are usally restore a backup (which is always the preferable way of resolving corruption) or copiying the data out and recreating the DB. Can't say more without seeing the checkDB output.

Can you post the full output of DBCC checkDB (< Database name > ) WITH No_INFOMSGS please.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nalini Devarakonda SSChasing Mays Points: 636 More actions · Answer 2

Here is the OUTPUt with the errors.

Server: Msg 8952, Level 16, State 1, Line 1

Table error: Database 'BESMgmt', index 'SyncServerState.PK_SyncServerState' (ID 946102411) (index ID 2). Extra or invalid key for the keys:

Server: Msg 8956, Level 16, State 1, Line 1

Index row (1:776:6) with values (Id = 11 and ServerConfigId = 0 and Type = 'DevMgmtServerConfigHistoryId') points to the data row identified by ().

Server: Msg 8952, Level 16, State 1, Line 1

Table error: Database 'BESMgmt', index 'SyncServerState.PK_SyncServerState' (ID 946102411) (index ID 2). Extra or invalid key for the keys:

Server: Msg 8956, Level 16, State 1, Line 1

Index row (1:776:7) with values (Id = 12 and ServerConfigId = 0 and Type = 'DevMgmtServerLastGUID') points to the data row identified by ().

Server: Msg 8952, Level 16, State 1, Line 1

Table error: Database 'BESMgmt', index 'SyncServerState.PK_SyncServerState' (ID 946102411) (index ID 2). Extra or invalid key for the keys:

Server: Msg 8956, Level 16, State 1, Line 1

Index row (1:776:8) with values (Id = 13 and ServerConfigId = 0 and Type = 'PolicyServerLastGroupingNumber') points to the data row identified by ().

CHECKDB found 0 allocation errors and 3 consistency errors in table 'SyncServerState' (object ID 946102411).

CHECKDB found 0 allocation errors and 3 consistency errors in database 'BESMgmt'.

repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (BESMgmt ).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It looks like all the errors are in the nonclustered indexes. I would suggest dropping and recreating all of the nonclustered indexes and constraints on the table 'SyncServerState' and recreate them. That includes the primary key

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nalini Devarakonda SSChasing Mays Points: 636 More actions · Answer 4

Thanks Shaw.

How ever the issue was resolved after doing DBCC DBREINDEX on SyncServerState Table.

SQL_Beginner SSC Enthusiast Points: 110 More actions · Answer 5

Hi Nalini,

Can I check with you what is the value for

1. DevMgmtServerConfigHistoryId

2. PolicyServerLastGroupingNumber

after you run the "dbcc dbreindex('TableName')?

Thanks.

SQL_Beginner SSC Enthusiast Points: 110 More actions · Answer 6

Its alright. Both value are still "NULL" after dbcc reindex. It somehow fix the corrupted table.

tosscrosby SSCoach Points: 18045 More actions · Answer 7

GilaMonster (7/23/2008)
It looks like all the errors are in the nonclustered indexes. I would suggest dropping and recreating all of the nonclustered indexes and constraints on the table 'SyncServerState' and recreate them. That includes the primary key

As Gail stated, it was the indexes, not the table. And her suggestion to rebuild them, you accomplished when you ran dbcc reindex - that's what cleared up your issue. And before running repair_allow_data_loss, you'd be wise to post and listen to the gurus. repair_allow_data_loss should be a last resort if no clean, viable backups are available.

-- You can't be late until you show up.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

And definitly not to be run when CheckDB says that repair_rebuild is sufficint.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tosscrosby SSCoach Points: 18045 More actions · Answer 9

Gail, would you recommend always running with repair_build or repair_fast? If not, why not? I've never used these options but in the OP's case, it might have fixed everything related to this issue the first time it was run. Just curious what your opinion would be. Thanks.

-- You can't be late until you show up.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Personally, I wouldn't run repair at all unless as an absolute last resort. If the corruption is in only nonclustered indexes, I would prefer to manually drop and recreate the indexes

That way there's no linguring doubt over exactly what the repair did.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rao.V Hall of Fame Points: 3452 More actions · Answer 11

Hi all,

I am facing the same issue at one of our customer servers.Repeatedly some of the application starts failing and when I ran DBCC CHECKDB it results out 22 Consistency errors.

Can anyone suggest me what wud be the possible reason and resolution??

The things I noticed:

1. We are receiving those errors in a single database everytime.

2.Those errors are getting fixed once after restoring the database with fresh backup file available.But the same issue is repeating again after some days.

3.Another fix we tried is we tried to keep the database in single user mode and have tried some of the options of DBCC(Which fixes temporarily this issue.)

Please suggest what can be done to fix this Permenantely

Thanks in Adavice!!!

tosscrosby SSCoach Points: 18045 More actions · Answer 12

Hardware is most always the culprit. Check your event logs for disk errors and run hardware diagnostics to see what causing your corruption issues.

-- You can't be late until you show up.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Check the windows event log, see if you have any drive errors. SQL doesn't corrupt its own files.

If the problem keeps reoccurring, I would suggest you move the DB only another machine

Corruption should always be fixed by restoring backups, not by running the repair options. They're a last resort and may cause data loss.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass