Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2011-09-23 : 16:55:28
|
| Hi,This a 2005 (SP3) databaseSee the following error in logs:SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x789a18d1; actual: 0x68fbcc4c). It occurred during a read of page (1:5074837) in database ID 6 at offset 0x000009adf2a000 in file 'E:\Microsoft SQL Server\Data\AVNAPPDB.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factorsActions:I ran DBCC CHECKDBResults: The corruption is limited to 1 table:CHECKDB found 0 allocation errors and 66 consistency errors in table 'MP_MPACWU_AC_WORKUNITS' (object ID 1129184660).Table error: Object ID 1129184660, index ID 1, partition ID 72057643774246912, alloc unit ID 72057643923079168 (type In-row data). The high key value on page (1:4914719) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:4914784).Need advise for best way to resolve. This table contains 43 million rows.?? Is this a good way to resolve the corruption ??:Option 1: ALTER DATABASE AVNAPPDBSET SINGLE_USER to ON;DBCC CheckDB ('AVNAPPDB', Repair_Allow_Data_Loss)Option 2: Drop the table and constraints.Recreate the table, repopulate table with data from the backup that was restored as a different db name.(I DO have a recent full backup made a few hour the corruption errors appeared. But I'm not certain that the table in the backup is not also corrupted.)Thanks for any advice.John |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-24 : 05:16:33
|
Please run the following and post the full and complete outputDBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS Do you have a clean backup (one you know does not have the corruption)?--Gail ShawSQL Server MVP |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-24 : 07:43:16
|
| " I'm not certain that the table in the backup is not also corrupted."You could restore the backup to a NEW, temporary, database name and run DBCC CHECKDB on that temp database to check (and you could restore earlier backups to check, if that one is still corrupted).If you are using FULL Recovery Model there is a good chance that you will be able to restore the most recent clean FULL backup, and all the TLog backups since then, and get a clean database. If you do that to your temporary database, and its still clean, you could then take a backup from that and restore over your corrupted database (or drop your database and rename the temporary one).You can make the test restores on a different machine (if you are short of disk space, or CPU time to run the CHECKDBs etc, on the main machine)If the restores work you will probably want to take a final TLog backup from the corrupted databse, to catch any recent changes (unless there haven't been any).Whatever caused the corruption still needs finding & fixing. Its usually a hardware fault, so will just reoccur if you don't get that sorted.Go with Gail's request. Gail has heaps of knowledge about this and will be able to help you get to the bottom of it, I'm sure.But you might want to be doing some trial restores, to a TEMP database name, in the meantime. |
|
|
|
|
|
|
|
|
Database Corruption - Incorrect Checksum