February 6, 2013 at 12:55 am
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.
February 6, 2013 at 1:01 am
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
February 6, 2013 at 8:28 am
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.
February 6, 2013 at 12:52 pm
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
February 6, 2013 at 2:15 pm
Did as you suggested, and problem was solved, just as you said.
Thanks so much for help!.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply