(I’m actually on-stage here at TechEd doing the DAT track pre-con with Kimberly – she’s on now until lunch so I’m catching up on forum problems…) Here’s a question that came up on of the SQLServerCentral.com corruption forums I monitor that I think is worth blogging about. To paraphrase: I have a bunch of corruptions in a database, that look like they’ve been there for a while. Repair is my only option – it works but I’d like to know what data is being deleted. How can I do that? Here are some of the errors: Server: Msg 8928, Level 16, State 1, Line 2 This is a clustered index that CHECKDB will repair by deleting pages at the leaf-level – essentially deleting a bunch of records. The pages look to be trashed (there were a bunch more errors that I didn’t include here that said the page headers were all corrupted – looked like the IO subsystem trashde a whole 64KB chunk of the disk) 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. To find the upper bound of the lower range: DBCC
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 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.
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. If you do a DBCC PAGE of these two pages, you can find the lower and upper bound of the clustered index key values that have been lost. Think of three ranges:
DBCC PAGE (‘dbname’, 1, 168575, 3);
GO
The key value in the slot at the end of output is the upper bound of the bottom range that’s intact.
Then do:
PAGE (‘dbname’, 1, 168583, 3);
DBCC
GO
The key value in the slot at the beginning of the output is the lower bound of the upper range that’s intact.
Everything in the middle will be deleted. You could also try a DBCC PAGE on the corrupt pages themselves too – you might be able to see some data in them.
I’ll be blogging a bunch more about repair after my corruption session this week at TechEd – watch this space!
6 thoughts on “CHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete”
Paul, one question – in your DBCC output, Object ID 645577338, index ID 0 – but you state that this is a clustered index page. Wouldn’t the index ID be 1 if the table had a clustered index ? ie is this a heap ?
Apoligies if this is my misunderstanding…
Sometimes for pages that can’t be processed in a clustered index leaf-level, the index ID is output as zero – especially in SQL Server 2000. The 8976 and 8978 errors only occur for B-tree (i.e. in this case, a clustered index) and state the true index ID.
Thanks
Paul,
A very useful blog. Just wanted to add. if you could include the out of DBCC PAGE command as well it would give a complete sense to the reader.
Regards,
Ajay..
I can’t for that database as it wasn’t mine – I’ll blog an example of doing this sometime in the next few weeks. Thanks
i get this error when i run dbcc checkdb , there is no object id, how to go about fixing this one
Msg 8939, Sev 16, State 98, Line 1 : Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -1339238865273094144 (type Unknown), page (65301:22416065). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -1. [SQLSTATE 42000]
That’s because the page header has been trashed. Best way to fix is to restore from backups. Without backups, there should be other errors so you can tell how much data you may lose if you have to run repair_allow_data_loss.