December 4, 2010 at 8:44 am
Hi guys,
The situation is,
1.Database is in suspect mode.
2.Simple recovery model.
3.when i ran checkdb against this database i got two pages which were corrupted.
4.pages cannot be restored due to simple recovery model.
5.now in the result of checkdb i will get page owners(tables...assumption).
6.can i drop those tables if they r not important and make the database online.
7.if i drop those tables will these pages get deallocated.
8.is this a correct solution.
December 4, 2010 at 10:52 am
You can't drop the tables because the database is suspect. While the database is in the suspect state you cannot even use it, much less change it. You can switch to emergency mode, but then the database is read only. You won't be able to drop that table until you get the database back online.
Two options:
1) Restore from your last good backup
2) If you are absolutely, completely, 150% sure that only those pages are corrupt, switch the database into emergency mode, run checkDB with the repair_allow_data_loss option. That will bring the database online and then you can drop that table.
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
December 4, 2010 at 10:59 am
but the repair option deallocates pages that are corrupt....
in other words deallocate whats broken link.....
correct me if i m wrong
December 4, 2010 at 11:09 am
Yes, that's exactly what it does. Deallocate the damaged pages and fix up the links.
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
December 5, 2010 at 12:15 pm
so the rows present in those pages will get lost.
and if the table is huge how we will find out which rows r gone.
December 5, 2010 at 12:23 pm
That's correct. That's why the option is REPAIR_ALLOW_DATA_LOSS
Very hard to figure out what's gone. If there's a clustered index you can figure out the range of rows that would be deleted. There's info on that on Paul Randal's blog. Otherwise restore a backup alongside and run a data comparison (like with Redgate's SQL Data compare)
That's why I suggest rather restore from your last good 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
December 5, 2010 at 1:36 pm
As Gail mentioned, you really need to restore from backup. At the very least, you'd need a backup to tell you what rows are gone. In this case, I would guess you need the restore just to get the database back online.
Paul's blog: http://www.sqlskills.com/BLOGS/PAUL/
December 8, 2010 at 9:43 am
when we run repair command,the corrupt pages get deallocated.
it means these pages get back to the disk.
is there a possibility for these pages to come again in the database file when it grows automatically.
and if not is there a threat of anykind because of these pages.
December 8, 2010 at 9:53 am
azad maratha (12/8/2010)
when we run repair command,the corrupt pages get deallocated.it means these pages get back to the disk.
No, it means those pages are no longer part of any objects within the database. They're free pages, available for reuse when necessary. CheckDB doesn't shrink the file and a shrink file is the only thing that releases part of the file back to the disk.
It's not the pages (the 8k chunks) that you need to be worrying about. An 824 (checksum) just means that the data on the page is not what's supposed to be there. It doesn't (automatically) mean that the portion of disk is faulty. It's the entire IO subsystem you need to be worying about as something overwrote those pages outside of SQL Server's control.
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
December 8, 2010 at 11:28 am
They're free pages, available for reuse when necessary.
how can they be reused if they r corrupt.
December 8, 2010 at 11:38 am
did you read Gilamonster's last note? The pages are not necessarily "corrupt" as in bad sectors on disk. SQL Server is not finding on those pages what it expects, so something wrote on them, or didn't write, when it was supposed to. If they were bad blocks, I'd expect the hardware to remap them to another part of the disk. Instead you have potentially a problem in the IO system (hardware or drivers/firmware) that caused something to be written to the page that wasn't what SQL Server expected.
December 8, 2010 at 11:45 am
ohhh...now i get it.and i apologize for my ignorance.
still so much to learn.
you guys are great.taking time to answer all our questions (sometimes stupid)
December 8, 2010 at 1:18 pm
Azad there are 2 different things -
1 page being corrupt
2 The sector on the HD being corrupt
If its just the page then u should be fine and can be fixed with data loss. But if its sector that needs to be fixed from OS/HD end.
bit late in responding...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply