Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

16 inconsistencies found running DBCC checkDB Expand / Collapse
Author
Message
Posted Monday, June 9, 2008 8:36 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:12 AM
Points: 750, Visits: 1,556

I was getting 16 inconsistency errors on one table - and it appears may have been for awhile..

I was able to repair with this (in test):

use sdhelpdesktest
dbcc checktable ('itsm_hist_info_servicecall' , repair_allow_data_loss)
go


Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168576) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168577) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168578) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168579) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168580) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168581) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168582) could not be processed. See other errors for details.
Server: Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 645577338, index ID 1. Page (1:168576) was not seen in the scan although its parent (1:165809) and previous (1:168575) refer to it. Check any previous errors.
Server: Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 645577338, index ID 1. Index node page (1:165809), slot 279 refers to child page (1:168577) and previous child (1:168576), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 645577338, index ID 1. Index node page (1:165809), slot 280 refers to child page (1:168578) and previous child (1:168577), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 645577338, index ID 1. Index node page (1:165809), slot 281 refers to child page (1:168579) and previous child (1:168578), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 645577338, index ID 1. Index node page (1:165809), slot 282 refers to child page (1:168580) and previous child (1:168579), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 645577338, index ID 1. Index node page (1:165809), slot 283 refers to child page (1:168581) and previous child (1:168580), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 645577338, index ID 1. Index node page (1:165809), slot 284 refers to child page (1:168582) and previous child (1:168581), but they were not encountered.
Server: Msg 8978, Level 16, State 1, Line 2
Table error: Object ID 645577338, index ID 1. Page (1:168583) is missing a reference from previous page (1:168582). Possible chain linkage problem.
Server: Msg 8939, Level 16, State 1, Line 2
Table error: Object ID 1797637899, index ID 0, page (1:168576). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 2
Table error: Object ID 1797637899, index ID 0, page (1:168576). Test ((m_type >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 2
Table error: Object ID 1797637899, index ID 0, page (1:168576). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 44 and 8028.
Server: Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 1797637899, index ID 0, page ID (1:168577). The PageId in the page header = (0:32).
Server: Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 1797637899, index ID 0, page ID (1:168578). The PageId in the page header = (0:32).
Server: Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 1797637899, index ID 0, page ID (1:168579). The PageId in the page header = (0:32).
Server: Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 1797637899, index ID 0, page ID (1:168580). The PageId in the page header = (0:32).
Server: Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 1797637899, index ID 0, page ID (1:168581). The PageId in the page header = (0:32).
DBCC results for 'ITSM_HIST_INFO_SERVICECALL'.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Repair: Page (1:168576) has been deallocated from object ID 645577338, index ID 0.
Repair: Page (1:168577) has been deallocated from object ID 645577338, index ID 0.
Repair: Page (1:168578) has been deallocated from object ID 645577338, index ID 0.
Repair: Page (1:168579) has been deallocated from object ID 645577338, index ID 0.
Repair: Page (1:168580) has been deallocated from object ID 645577338, index ID 0.
Repair: Page (1:168581) has been deallocated from object ID 645577338, index ID 0.
Repair: Page (1:168582) has been deallocated from object ID 645577338, index ID 0.
Clustered index successfully restored for object 'dbo.ITSM_HIST_INFO_SERVICECALL' in database 'SDHELPDESKTEST'.
There are 659196 rows in 8759 pages for object 'ITSM_HIST_INFO_SERVICECALL'.
CHECKTABLE found 0 allocation errors and 15 consistency errors in table 'ITSM_HIST_INFO_SERVICECALL' (object ID 645577338).
CHECKTABLE fixed 0 allocation errors and 15 consistency errors in table 'ITSM_HIST_INFO_SERVICECALL' (object ID 645577338).
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Server: Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 1797637899, index ID 0, page ID (1:168582). The PageId in the page header = (0:32).
The error has been repaired.
CHECKTABLE found 0 allocation errors and 9 consistency errors in table '(Object ID 1797637899)' (object ID 1797637899).
CHECKTABLE fixed 0 allocation errors and 9 consistency errors in table '(Object ID 1797637899)' (object ID 1797637899).

what I'm struggling with is how do I figure out what (if any) data I lost?



Post #513758
Posted Monday, June 9, 2008 9:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
Hi,

This is a clustered index that it's repairing by deleting pages at the leaf-level - essentially deleting a bunch of records. The pages look to be trashed so there's nothing much else you can do. As the table has a clustered index, you can use the error messages to find the pages on either 'logical' side of the pages being deleted - and hence figure out the range of records that have been deleted.

The errors show that pages 168576 through 168582 in file 1 are corrupt. There are also errors that say the previous page of 168576 is 168575, and the next page of 168582 is 168583. I fyou do a DBCC PAGE of these two pages, you can find the lower and upper bound of clustered index key values that have been lost. Think of three ranges:
- lower range that's intact
- range to be deleted
- upper range that's intact

Do this with:

DBCC TRACEON (3604); -- allows the output to come to the console
DBCC PAGE (sdhelpdesktest, 1, 168575, 3);
GO

The key value in the slot at the end of output is the end of the bottom range that's intact. Then do:

DBCC PAGE (sdhelpdesktest, 1, 168583, 3);
GO

The key value in the slot at the beginning of the output is the start of the upper range that's intact.

Everything in the middle will be deleted. You could try a DBCC PAGE on the corrupt pages themselves too - you might be able to see some data in them. Looks like your IO subsystem trashed a contiguous 64KB block.

Hope this helps!


Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #513809
Posted Saturday, May 10, 2014 12:03 AM This worked for the OP Answer marked as solution
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 11:08 PM
Points: 26, Visits: 42
hi
As you know that DBCC CHECKDB Command's checks the logical and physical consistencies of the pages, clustered index etc. if he finds some some change or check fails then immediately it report an error. The reason check fails due to hardware corruption, high leval od corruption etc. So in curious and serios situation i like to prefer SQL recovery relevance.


SSMS Expert
Post #1569491
Posted Saturday, May 10, 2014 3:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 39,886, Visits: 36,233
Please note: 6 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1569503
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse