SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Severe error occurred on the current command. How bad is corruption fix?


Severe error occurred on the current command. How bad is corruption fix?

Author
Message
bill-900846
bill-900846
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 63
Running on Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

I ran DBCC CHECKDB (CHRUSAPP) WITH NO_INFOMSGS, ALL_ERRORMSGS
which returned the following results:
Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1102783136, index ID 1, partition ID 72057594172211200, alloc unit ID 72057594174046208 (type In-row data).
The previous link (1:15001) on page (1:13954) does not match the previous page (1:4080) that the parent (1:28791), slot 35 expects for this page.
Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1102783136, index ID 1, partition ID 72057594172211200, alloc unit ID 72057594174046208 (type In-row data).
B-tree chain linkage mismatch. (1:4080)->next = (1:13954), but (1:13954)->Prev = (1:15001).
CHECKDB found 0 allocation errors and 2 consistency errors in table 'PJRATE' (object ID 1102783136).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'CHRUSAPP'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (CHRUSAPP).

SQL Server Management Studio.Management.Maintenance Plans shows same error since 1/9/2013.

I checked for duplicate keys and didn't find any.

Would a DBCC DBREINDEX('PJRATE') likely correct the error? BTW, PJRATE is a master table with 3,722 rows. We could delete all data through the application UI and then copy/paste or import, again through the UI, if we had to.

Are these errors indicative of hard disk failures?

Thanks in advance.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87255 Visits: 45272
That one is trivial to fix fortunately
Take the DB into single user mode and run DBCC CheckDB with the repair_rebuild option.

Drive failure, probably not. Something wrong somewhere in the IO stack, probably yes.

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


bill-900846
bill-900846
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 63
Gail, thanks for prompt response.

I think you are right about IO stack and not hard drive. Customer created a test DB from backup of prod DB made after problem arose. We get same error in test DB.

The subject DB is created and maintained by a ERP system that does not use database referential integrity. Another option I thought of is:
a. Script all indexes from offending table PJRate
b. Create backup of PJRate (select * into xPJRate from PJRate)
c. Create indexes on xPJRate
d. Drop table PJRate
e. Rename xPJRate to PJRate.

Since the DBCC CHECKDB messages show the error on a clusteded index, my alternative approach may not be any betty than dropping indexes on offending table and then rebuilding.

Thanks.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87255 Visits: 45272
Just run the CheckDB. If it was a NC index I'd say drop and recreate, but a cluster that's a lot of time and a lot of log usage, you're rebuilding the entire table and every single nonclustered index twice if you drop the cluster and recreate. There's no data loss potential here.

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


bill-900846
bill-900846
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 63
Did as you suggested, and problem was solved, just as you said.

Thanks so much for help!.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search