April 27, 2015 at 8:44 am
hello,i have a SQL 2008 R2 RTM production instance, in which we run dBCC CheckDB every weekend to check on the DB.
This weekend this sql job returned the error:
DBCC RESULTS
--------------------
<DbccResults>
<Dbcc ID="0" Error="8928" Severity="16" State="1">Object ID 866531312, index ID 1, partition ID 72057602979266560
, alloc unit ID 72057603064397824 (type In-row data): Page (1:7650240) could not be processed. See other errors
for details.</Dbcc>
<Dbcc ID="1" Error="8939" Severity="16" State="98">Table error: Object ID 866531312, index ID 1, partition ID 720
57602979266560, alloc unit ID 72057603064397824 (type In-row data), page (1:7650240). Test (IS_OFF (BUF_IOERR, pB
UF->bstat)) failed. Values are 12716041 and -6.</Dbcc>
<Dbcc ID="2" Error="8990" Severity="10" State="1">CHECKDB found 0 allocation errors and 2 consistency errors in t
able 'tblDistpatch' (object ID 866531312).</Dbcc>
We tried to rebuild the indexes in the table:
tblDistpatch
the non clustered indexes ran fine however the cluster index rebuilt returned an error:
Error: The statement has been terminated.
Msg 829, Level 21, State 1, Line 1
Database ID 3, Page (1:7650240) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.
in TEST environment
we were able to reproduce this error by restoring latest backup.
we ran :
dbcc checktable (tblDistpatch ,REPAIR_ALLOW_DATA_LOSS )
then we ran dbcc checkdb and no errors where found.
my question comes with in your experience is this the best possible way to fix this table?
good news is only one table, but running this in production environment will required to put db in single user mode first
and that will be complete outage of environment. (application, replication, etc).
April 27, 2015 at 10:17 am
Be extremely cautious using repair_allow_data_loss. There is zero guarantee that it will fix the problem by dropping one or two pages worth of data. It can, in some cases, nuke the entire database. My first resort would be to restore the database from a tested good backup. Or, drop the table, run DBCC to see if that resolves the issue, then restore the database to a second location and copy the data over from the original table. Either of these is safer than doing what you're proposing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2015 at 10:50 am
thank you Grant for your reply. this is a 2 Tb db, this table in question is 15 Gbs. I will try your second approach.
"Drop the table, run DBCC to see if that resolves the issue, then restore the database to a second location and copy the data over from the original table."
can I create a table from scratch with different name. then import the data from corrupted table. when completed drop corrupted table and rename new table with original name? Or since I have corrupted pages this will not work?
April 27, 2015 at 10:56 am
DBA-640728 (4/27/2015)
thank you Grant for your reply. this is a 2 Tb db, this table in question is 15 Gbs. I will try your second approach."Drop the table, run DBCC to see if that resolves the issue, then restore the database to a second location and copy the data over from the original table."
can I create a table from scratch with different name. then import the data from corrupted table. when completed drop corrupted table and rename new table with original name? Or since I have corrupted pages this will not work?
Yeah, you should be able to do that. As long as the corrupted pages are only within the one database object. Personally, I'd be more comfortable just dropping it first as a safety measure just in case a page split or something moves the corruption along. It sounds like, since you can restore a corrupted database, that the corruption is logical, not physical, so the likelihood is that it will stay within the table. But there are no guarantees there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2015 at 1:16 pm
+1 to Grant's recommendations.
My curiosity's piqued about one detail from the original post, though. Is the following correct?
DBA-640728 (4/27/2015)
Database ID 3, Page (1:7650240) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.
Database ID 3 would be model, which would be an odd database for all that user data. 🙂
April 27, 2015 at 4:31 pm
good catch, you are correct! the database id is not 3. It is a user db.
Thank you Grant for all your help! really appreciate it. I am going to try your approach.
April 29, 2015 at 4:05 pm
Update: It was too hard to go with the approach of importing the data from the table with the corrupted page into a new table. This table had many dependent tables with foreign keys involved and also many triggers.
Also, an error will keep popping out every time we were trying to select data from this table. So all data after the date of page corruption was not available. Even though you were able to do updates, deletes, go figure...
I tried the page restore approach since by checking into msdb..suspect_pages, the issue was with only ONE page.
I restored the page by using the latest full backup. I did a full backup before doing the page restore.
and that seem to work. Run DBCC CHECKDB and no errors!
What's your take on page restores? is it less risky than doing a CHECKTABLE REPAIR_ALLOW_DATA_LOSS?
Can we do the page restore with the DB Online? or does it have to be put in single user?
April 29, 2015 at 4:08 pm
I'm going to punt. I haven't used page restore in a production system, so I'm not going to try to advise you since important data is on the line.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply