June 9, 2008 at 8:36 am
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?
June 9, 2008 at 9:33 am
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
May 10, 2014 at 12:03 am
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
May 10, 2014 at 3:32 am
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
March 13, 2017 at 12:19 pm
March 13, 2017 at 1:21 pm
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
March 13, 2017 at 1:28 pm
ok, done
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply