June 25, 2012 at 4:31 am
One of our databases is reporting the following errors when I run DBCC CHECKDB:
Object ID 773577794, index ID 1, partition ID 72057594040156160, alloc unit ID 72057594041729024 (type In-row data): Page (1:31189) could not be processed. See other errors for details.
Table error: Object ID 773577794, index ID 1, partition ID 72057594040156160, alloc unit ID 72057594041729024 (type In-row data), page (1:31189). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 381814793 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'Address' (object ID 773577794).
SQL Server is specifying that I should use repair_allow_data_loss to fix the issue. I have read Gail's article[/url] about fixing corrupt databases so I think there is maybe some incorrect data in one of the columns.
Is there anything else I can do to try and pinpoint this?
The table is quite large so I don't really fancy trawlling through all the records. If there is any other information I can provide to help let me know.
Thanks in advance
June 25, 2012 at 5:01 am
No, that's not incorrect data in a column, that's a corrupt page.
If it was an out of range data type in a column, the error would read "Column "<column name>" value is out of range for data type "<data type>". Update column to a legal value."
You can repair and have that page (and any data on it discarded) or you can restore from backup. If you're in full recovery model and have an unbroken chain log backups from a clean full backup, consider a single page restore.
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
June 25, 2012 at 5:11 am
Hi Gail,
Thanks for your reply.
I'm going to have to repair it and have the page discarded as it's in simple recovery mode. This isn't a massive issue but I was interested to see if there was any clever way of fixing it apart from restoring as you suggested.
How would the page become corrupted? Or is that impossible to answer?
Chris
June 25, 2012 at 6:15 am
Chris-475469 (6/25/2012)
This isn't a massive issue but I was interested to see if there was any clever way of fixing it apart from restoring as you suggested.
Hex editing the data page if you're good enough to pull that off... 🙂
What you can do is repair, then restore your last clean backup to another DB and compare the tables, maybe you can replace what repair discarded
How would the page become corrupted? Or is that impossible to answer?
99% chance IO subsystem problem of some form.
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
June 25, 2012 at 6:27 am
Not sure I know enough about the first option you suggested but I'll have a look into it anyway.
Thanks again for the info
June 25, 2012 at 6:36 am
Chris-475469 (6/25/2012)
Not sure I know enough about the first option you suggested
Neither do I. It's something that maybe 10 people in the world can do and most work for MS.
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
July 4, 2012 at 6:01 am
You can also try and do a backup and restore(From one server to another), see if you get error message when doing a dbcc on a new database
July 4, 2012 at 8:43 am
Won't help. It's a logical error (page with garbage on it), so the garbage will be included in the backup and restored on the other server.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply