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


16 inconsistencies found running DBCC checkDB


16 inconsistencies found running DBCC checkDB

Author
Message
Jpotucek
Jpotucek
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1560 Visits: 1677
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?



Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3647 Visits: 1717
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
Elliswhite
Elliswhite
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 54
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
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: 87137 Visits: 45267
Please note: 6 year old thread.

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


daniel 73468
daniel 73468
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 14

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: 87137 Visits: 45267
Please post new questions in a new thread. Thank you

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


daniel 73468
daniel 73468
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 14
ok, done
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