DBCC CHECKDB errors

Question

DBCC CHECKDB errors

alinusha_sibi

Old Hand

Points: 329
More actions
June 29, 2011 at 8:35 am

#240059

Hello,
I have been trying to troubleshoot some consistency errors in one database.
I attached the output for DBCC CHECKDB ('Test_DataCorrupt') WITH NO_INFOMSGS, ALL_ERRORMSGS.
Can anyone help me narrow down what needs fixing first, or how to repair this db?
Thank you,
Alina

Viewing 10 posts - 1 through 9 (of 9 total)

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

addisionphilip SSC Veteran Points: 243 More actions · Answer 1

Hello Alina,

The best solution to troubleshoot consistency errors reported by DBCC CHECKDB is to restore from known good backup. However, if you are not able to restore from the backup then use minimum repair level offered by CHECKDB.

Note: First check the system problems such as file system problem, hardware problem then restore or repair.

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

Interesting set of errors.

Try dropping, then recreating the clustered index on MultiSpuDownloads and dropping and recreating the nonclustered index (ID 3) on MultiSpuEndpointProperties

Then run CheckDB again (with the same options, no repair) and see what is still broken

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

alinusha_sibi Old Hand Points: 329 More actions · Answer 3

Hi,

Thank you for your suggestions. Here is what I have tried to do:

1) Try dropping and recreate the nonclustered index (ID 3) on MultiSpuEndpointProperties table

I used the following:

USE Test_DataCorrupt

GO

CREATE NONCLUSTERED INDEX [U_IXN_MultiSpuEdptProp_spuId] ON [dbo].[MultiSpuEndpointProperties]

(

[spuId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)

ON [PRIMARY]

GO

Results:

Command(s) completed successfully.

2)Try dropping and recreate the clustered index on MultiSpuDownloads

USE Test_DataCorrupt

GO

CREATE CLUSTERED INDEX [NU_IXC_MultiSpuDownloads_A] ON [dbo].[MultiSpuDownloads]

(

[channel] ASC,

[state] ASC,

[snr] DESC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

GO

Results:

Msg 9100, Level 23, State 2, Line 1

Possible index corruption detected. Run DBCC CHECKDB.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

After this, executing again DBCC CHECKDB ('Test_DataCorrupt') WITH NO_INFOMSGS, ALL_ERRORMSGS

the error messages still persist, and dropping & recreating the clustered index on MultiSpuDownloads table had no effect, even if I didn't receive any error message after executing the command, as you can see above.

I also notice something weird for [dbo].[MultiSpuDownloads] . If I try to identify if I have duplicates keys in this table, using the query bellow:

SELECT MultiSpuDownloadID

FROM [dbo].[MultiSpuDownloads]

GROUP BY MultiSpuDownloadID

HAVING COUNT(MultiSpuDownloadID) > 1

i don't receive any results

But if I do something like :

SELECT *

INTO #test

FROM dbo.MultiSpuDownloads

SELECT MultiSpuDownloadID

FROM #test

GROUP BY MultiSpuDownloadID

HAVING COUNT(MultiSpuDownloadID) > 1

i receive around 300 duplicates

Also, I've tried to put the database in single_user mode and run DBCC CHECKTABLE ('MultiSpuEndpointProperties ', REPAIR_REBUILD), i didn't received any errors here, but after I executed DBCC CHECKDB the errors received the first time are showing up again. So it seemed like the repair_rebuild command didn't had any effect.

Thank you,

Alina

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

CheckDB with repair_rebuild will have no effect. The output of checkDB states that the minimum level to repair is repair_allow_data_loss, hence running a lesser repair level will not do anything.

Got a clean backup? If not, try checkDB with repair_allow_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

alinusha_sibi Old Hand Points: 329 More actions · Answer 5

Hi,

Unfortunately we don't have a clean backup.

I've tried to perform checkDB with repair_allow_data_loss, but error messages were returned.

-- Alina

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 6

maybe this link can help: http://support.microsoft.com/kb/923247/en-us#top

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

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

alinusha_sibi (7/4/2011)
I've tried to perform checkDB with repair_allow_data_loss, but error messages were returned.

Could you be a bit more specific than 'error messages were returned'?

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

addisionphilip SSC Veteran Points: 243 More actions · Answer 8

addisionphilip

SSC Veteran

Points: 243

July 6, 2011 at 11:12 pm

#1349427

Hello Alina,

Have you fixed the problem?

Soonyu-163551 SSC Eights! Points: 811 More actions · Answer 9

In your posting you mention you drop and rebuilt clustered index

You should drop and rebuilt this index instead,

select object_name(1649597115)

table :MultiSpuDownloads

index :U_IXN_MultiSpuEdptProp_spuId

Your dbcc checkdb result mention that

Table error: Table 'MultiSpuEndpointProperties' (ID 1697597286). Data row does not have a matching index row in index 'U_IXN_MultiSpuEdptProp_spuId' (ID 3).