April 10, 2011 at 10:44 pm
Hi Experts,
One of our production DB went to suspect mode .Please help in recovering the DB.
We madfe it to EMERGENCY mode and did a checkdb, got the error
DBCC results for 'TABLE'.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 382624406, index ID 1, partition ID 72057601842544640, alloc unit ID 72057600960888832 (type In-row data). Page (4:24331539) is missing a reference from previous page (4:38440716). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 1
Object ID 382624406, index ID 1, partition ID 72057601842544640, alloc unit ID 72057600960888832 (type In-row data): Page (4:38440716) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 382624406, index ID 1, partition ID 72057601842544640, alloc unit ID 72057600960888832 (type In-row data), page (4:38440716). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 46139401 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 382624406, index ID 1, partition ID 72057601842544640, alloc unit ID 72057600960888832 (type In-row data). Page (4:38440716) was not seen in the scan although its parent (4:24330569) and previous (4:24331538) refer to it. Check any previous errors.
Msg 8928, Level 16, State 1, Line 1
Object ID 382624406, index ID 238, partition ID 72057601735065600, alloc unit ID 72057600867368960 (type In-row data): Page (90:14607230) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 382624406, index ID 238, partition ID 72057601735065600, alloc unit ID 72057600867368960 (type In-row data), page (90:14607230). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 130025481 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 382624406, index ID 238, partition ID 72057601735065600, alloc unit ID 72057600867368960 (type In-row data). Page (90:14607230) was not seen in the scan although its parent (90:14588297) and previous (90:14607229) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 382624406, index ID 238, partition ID 72057601735065600, alloc unit ID 72057600867368960 (type In-row data). Page (90:14607231) is missing a reference from previous page (90:14607230). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 1
Object ID 382624406, index ID 238, partition ID 72057601735065600, alloc unit ID 72057600867368960 (type In-row data): Page (90:14880795) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 382624406, index ID 238, partition ID 72057601735065600, alloc unit ID 72057600867368960 (type In-row data), page (90:14880795). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 130025481 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 382624406, index ID 238, partition ID 72057601735065600, alloc unit ID 72057600867368960 (type In-row data). Page (90:14880795) was not seen in the scan although its parent (84:6121912) and previous (90:14880794) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 382624406, index ID 238, partition ID 72057601735065600, alloc unit ID 72057600867368960 (type In-row data). Page (90:14880796) is missing a reference from previous page (90:14880795). Possible chain linkage problem.
Weare not to do DBCC CHECKTABLE with REPAIR option.
Please Help
TIA
April 10, 2011 at 10:59 pm
Since it was suspect your only two options are:
1) CheckDB with REPAIR_ALLOW_DATA_LOSS. There's damage to the clustered index (just one page) and that's the only repair level allowed in emergency mode.
2) Restore from backup. (page if the DB is in full recovery and you have log backups, you'll need to restore 3 pages)
Don't forget to do some root cause analysis to identify the reason for the corruption.
p.s. Index id 238??? You have a table with 238 indexes on it? What the hell for?
90 files?
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
April 10, 2011 at 11:25 pm
GilaMonster (4/10/2011)
Since it was suspect your only two options are:1) CheckDB with REPAIR_ALLOW_DATA_LOSS. There's damage to the clustered index (just one page) and that's the only repair level allowed in emergency mode.
2) Restore from backup. (page if the DB is in full recovery and you have log backups, you'll need to restore 3 pages)
Don't forget to do some root cause analysis to identify the reason for the corruption.
p.s. Index id 238??? You have a table with 238 indexes on it? What the hell for?
90 files?
Thanks Gail for the Quick reply.
Is there anyway to recover without data loss and without restoring.
How can i restore only 3 pages? Yes the db in full recovery and we have log backups
This is a VLDB more than 1TB in size.
There are only 32 indexes in that particular table. I didnt get your question 90 files?
Can you please help me in find the root cause?
April 11, 2011 at 2:58 am
Ratheesh.K.Nair (4/10/2011)
Is there anyway to recover without data loss and without restoring.
No. Those weren't the two best options I gave above. They're the only options. You can repair or you can restore (database, filegroup, file or page)
How can i restore only 3 pages? Yes the db in full recovery and we have log backups
Read up on Page restores in Books Online.
There are only 32 indexes in that particular table. I didnt get your question 90 files?
Are you certain? With an index is of over 200 at some point at least there were more than that.
The file ID is 90, that means that there were (or are) 90 files in that database. Is that intentional?
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
April 11, 2011 at 3:15 am
Are you certain? With an index is of over 200 at some point at least there were more than that.
Now the DB is in single user mode so cant check now. Earlier i have scripted all the indexes in that i found only 32.
The file ID is 90, that means that there were (or are) 90 files in that database. Is that intentional?
Not 90 ,around 20 files.
April 12, 2011 at 12:22 am
We did dbcc checkdb with allo data loss and that repaired the db with loss of 2Lakh records.
April 13, 2011 at 2:06 am
Can you restore a damage page using below script...
RESTORE DATABASE dbccpagetest PAGE = '58' FROM DISK = 'C:\dbccpagetest.bak';
after that restore your database..
-- Need to complete roll forward. Backup the log tail...
BACKUP LOG dbccpagetest TO DISK = 'C:\dbccpagetest_log.bak' WITH INIT;
GO
RESTORE LOG dbccpagetest FROM DISK = 'C:\dbccpagetest_log.bak';
April 13, 2011 at 2:29 am
shiv-356842 (4/13/2011)
Can you restore a damage page using below script...RESTORE DATABASE dbccpagetest PAGE = '58' FROM DISK = 'C:\dbccpagetest.bak';
after that restore your database..
-- Need to complete roll forward. Backup the log tail...
BACKUP LOG dbccpagetest TO DISK = 'C:\dbccpagetest_log.bak' WITH INIT;
GO
RESTORE LOG dbccpagetest FROM DISK = 'C:\dbccpagetest_log.bak';
Between the restore of the full DB backup and the backup of the tail of the log, you need to restore all other log backups taken since that full 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
April 13, 2011 at 3:10 am
Thanks Gail,
Our onsite team is doing the restore between i have a doubt,Is it a problem if any job try to access the table or that particular database while restoring?
According to my knowledge while restoring noone will be able to access the DB.
They are much more senior than me and they are directly restoring over the same DB.
Is there anyway that the present DB will made available and will create and new DB and restore the backups and can take only those 2Lakh records and insert to original DB?? Is there anyway to compare the table in present DB and the DB restored with backup??
TIA
April 13, 2011 at 3:23 am
Ratheesh.K.Nair (4/13/2011)
Thanks Gail,Our onsite team is doing the restore between i have a doubt,Is it a problem if any job try to access the table or that particular database while restoring?
Page or full database restore?
According to my knowledge while restoring noone will be able to access the DB.
Depends on the edition of SQL and the type of pages restoring (assuming you're restoring pages). But the DB is suspect, so it's unavailable anyway.
Is there anyway that the present DB will made available and will create and new DB and restore the backups and can take only those 2Lakh records and insert to original DB?? Is there anyway to compare the table in present DB and the DB restored with backup??
Sure. CheckDB with repair allow data loss on the DB, restore the backup alongside, use something like Redgate's SQL data compare to sync.
Almost certainly far more time consuming than restoring pages, if that's the alternative you're considering.
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
April 13, 2011 at 4:17 am
Gail Thanks a lot in guiding me..
From mail communication i think they are doing full restore.
Now the DB is not in suspect .We made the DB available using dbcc checkdb with allow data loss.
The server is 2005 Enterprise.
We have full and transaction log backups .
Hope below are the page resore options my cause
RESTORE DATABASE DB_NAME PAGE = '4:38440716' FROM DISK = 'C:\backup.bak';
RESTORE DATABASE DB_NAME PAGE = '90:14607230' FROM DISK = 'C:\backup.bak';
RESTORE DATABASE DB_NAME PAGE = '90:14880795' FROM DISK = 'C:\backup.bak';
April 13, 2011 at 4:25 am
You can't restore pages then, that was an option when they were corrupt, not now.
Because of the log restore (and because checkDB is logged), if you restore pages now you will just end up with deallocated pages, exactly as you have after the repair.
Restore as a new DB, then copy over the rows that repair discarded, something like SQL data compare will work.
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
April 13, 2011 at 4:54 am
Thanks a lot Gail.
Much appreciate the way you came with me in solving my issue. Once a again thanks a lot 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply