February 17, 2012 at 5:58 pm
Our nightly check db failed last night because of a corrupted page in one of the table. Following are the details:
DBCC results for 'TableX'.
Msg 8928, Level 16, State 1, Line 1
Object ID 1211151360, index ID 1, partition ID 72057606682378240, alloc unit ID 72057606848970752 (type In-row data): Page (1:13175266) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1211151360, index ID 1, partition ID 72057606682378240, alloc unit ID 72057606848970752 (type In-row data), page (1:13175266). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1211151360, index ID 1, partition ID 72057606682378240, alloc unit ID 72057606848970752 (type In-row data). Page (1:13175266) was not seen in the scan although its parent (1:4790005) and previous (1:13175265) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1211151360, index ID 1, partition ID 72057606682378240, alloc unit ID 72057606848970752 (type In-row data). Page (1:13175267) is missing a reference from previous page (1:13175266). Possible chain linkage problem.
There are 109395 rows in 42212 pages for object 'TableX'
CHECKDB found 0 allocation errors and 4 consistency errors in table 'TableX' (object ID 1211151360).
This is a SQL 2008 R2 server and we do have the option to restore the damaged page from backup. Paul Randal has this beautiful blog on that:
We have the full backup of the DB from a couple of days with teh clean page, we also have all the T log backups from that full backup until this point to successfully complete the page restore. I even tested this on my test server and it works fine.
But the concern I have to do a page restore on my production database is that this table on my production database is replicated (Transactional) to our reporting instance. I am not sure what impact the page restore and applying all the T Logs has on replication.
Does any one know of any possible impact on replication??
February 18, 2012 at 2:26 pm
I checked with an expert and he's pretty sure that the page restores won't affect the replication, the page restore will just be bringing the page up to current state with log records that the log reader has already processed.
p.s. You do need to do some root-cause analysis. Corruption doesn't just happen and you do want to find the cause before something worse happens.
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
February 18, 2012 at 6:29 pm
Thx for the reply Gail Shaw. I was able to successfully completed the page restore with no impact to replication. This is the first occurance we had on this server and I will follow up with our SAN team to see if they can do any Root Cause Analysis on the SAN frame.
Are there any other precautions we could take from a SQL Server standpoint??
February 19, 2012 at 3:18 am
Check event logs and driver versions on the server too. Cause may not be the SAN, could be anything in the IO stack from filter drivers right down to the disks
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply