August 27, 2009 at 10:01 am
I am dealing with corruption in one of my tables that was flagged when I ran a DBCC CHECKDB. I backed up the database and restored it to a test version and ran a DBCC CHECKTABLE with the REPAIR_ALLOW_DATA_LOSS option. Here is a sample of the output:
DBCC results for 'JournalArchive'.
Repair: Deleted record for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data), on page (1:327), slot 5. Indexes will be rebuilt.
Repair: Deleted off-row data column with ID 183229939712, for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data) on page (1:327), slot 5.
Repair: Deleted off-row data column with ID 183230005248, for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data) on page (1:327), slot 5.
Repair: Deleted record for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data), on page (1:9646), slot 25. Indexes will be rebuilt.
Repair: Deleted off-row data column with ID 183114334208, for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data) on page (1:9646), slot 25.
Repair: Deleted off-row data column with ID 183114399744, for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data) on page (1:9646), slot 25.
Repair: Deleted record for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data), on page (1:9646), slot 26. Indexes will be rebuilt.
Repair: Deleted off-row data column with ID 183115120640, for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data) on page (1:9646), slot 26.
Repair: Deleted off-row data column with ID 183115186176, for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data) on page (1:9646), slot 26.
Repair: Deleted record for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data), on page (1:9646), slot 27. Indexes will be rebuilt.
Repair: Deleted off-row data column with ID 183115382784, for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data) on page (1:9646), slot 27.
Repair: Deleted off-row data column with ID 183115448320, for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data) on page (1:9646), slot 27.
How do I interpret this output to know what data it is repairing/deleteing?
Thanks for your help!
August 27, 2009 at 10:38 am
From what I can tell it's deleted 4 rows from the table (a heap, if I'm not mistaken) and their corresponding off-row (lob columns or row overflow) data.
These are the four rows deleted:
Deleted record for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data), on page (1:327), slot 5
Deleted record for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data), on page (1:9646), slot 25
Deleted record for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data), on page (1:9646), slot 26.
Deleted record for object ID 2009058193, index ID 0, partition ID 72057594038779904, alloc unit ID 413140614447104 (type In-row data), on page (1:9646), slot 27.
Do you not have a clean backup that you can rather restore from?
What was the original output of CheckDB?
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
August 28, 2009 at 8:09 am
Yup - that's what it did. The output from repair is very simple to read, just the output from the consistency checks that's complicated 🙂
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
August 28, 2009 at 8:46 am
Thanks for the replies. I figured the rows and the off-row data were being deleted, but is there a way to find the values of the columns being deleted so i can warn the users what data will be missing?
August 28, 2009 at 8:56 am
Not that I'm aware of, from the output of the repair.
Do you have the original error messages?
Why repair and not restore from backup?
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
August 28, 2009 at 9:38 am
If you have an older backup - you *might* be able to restore it and check to see if that page has some of the deleted rows on - but no guarantee they're up-to-date.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply