| Author |
Topic |
|
wiftody
Starting Member
5 Posts |
 Posted - 2008-07-21 : 15:20:46
|
| I received the following error message in an application running against one of my databases:SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xfdff74c9; actual: 0xfdff74cb). It occurred during a read of page (1:69965) in database ID 5 at offset 0x0000002229a000 in file 'e:\SQL Server\MSSQL.1\MSSQL\[DB_Name].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 factors; for more information, see SQL Server Books Online. That sounded bad so I ran the following:DBCC CHECKDB ([DB_Name]) WITH NO_INFOMSGS, ALL_ERRORMSGSAnd received the following report:Msg 8928, Level 16, State 1, Line 1Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID 72057594055557120 (type LOB data): Page (1:69965) could not be processed. See other errors for details.Msg 8939, Level 16, State 98, Line 1Table error: Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID 72057594055557120 (type LOB data), page (1:69965). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID 72057594055557120 (type LOB data). The off-row data node at page (1:69965), slot 0, text ID 89622642688 is referenced by page (1:69968), slot 1, but was not seen in the scan.Msg 8929, Level 16, State 1, Line 1Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID 72057594055622656 (type In-row data): Errors found in off-row data with ID 89622642688 owned by data record identified by RID = (1:77754:1)CHECKDB found 0 allocation errors and 4 consistency errors in table '[Table_Name]' (object ID 1326627769).CHECKDB found 0 allocation errors and 4 consistency errors in database '[DB_Name]'.repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB ([DB_Name]).I looked through my old images, and it appears the error has existed for quite a while. I don't have a clean image. I'm not much of a DBA so I don't know the best way to proceed; I don't even understand how the error messages can point me to the data corruption. Can anyone tell me what these messages indicate and how to proceed? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-22 : 02:53:34
|
| The messages indicate that there is some form of corruption within the database. Vaules are not what they are supposed to be, pointers are pointing at pages that don't exist. Corruptions are normally cause by hardware problems, especially storage. The checksum error indicates that the page was changed sometime after SQL wrote it to disk.Since you don't have a clean backup, the only way forward at this point is to run checkDB with the allow_data_loss option. Note, you will lose data. How much and what I can't tell you.It'll take a while to run. You will need to have the database in single user mode during the repair so no one will be able to acccess it. Do you have available downtime?Suggestions going forward, once you've cleaned the corruption - run CheckDB regularly on all databases. The best way to recover from corruption is to restore a backup from before the corruption happened. To do that, you have to know fairly quickly when there's a problem and know when it started.--Gail ShawSQL Server MVP |
 |
|
|
wiftody
Starting Member
5 Posts |
Posted - 2008-07-22 : 12:55:43
|
| Well I was hoping there was more I could do than that. Too bad for me I guess. I'll see what happens with repair_allow_data_loss. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-22 : 13:36:40
|
| You may want to take a look at the system event logs, see if you have any hardware-related errors. Something caused the corruption. It's not a common happening.However if you don't know when the corruption happened, it can be hard to pinpoint the cause.--Gail ShawSQL Server MVP |
|
|
|
wiftody
Starting Member
5 Posts |
Posted - 2008-07-22 : 14:24:54
|
| I've just recently moved the database over to a new server. The corruption happened on the old server. We've changed servers because of ongoing hardware related problems. I guess that is the likely culprit. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-23 : 02:17:03
|
| Very likely. Do you have any other databases that were on that old server? If so, run checkdb on them too. Just to be sure--Gail ShawSQL Server MVP |
|
|
|
wiftody
Starting Member
5 Posts |
Posted - 2008-07-23 : 15:10:14
|
| I had already done that. Although the database is backed up regularly, it appears that the problem is quite old. I keep historical backups 1 month, all corrupt. Really, database copies older than a week, maybe 2, would require a massive amount of updates to get up to speed.I ran the repair_allow_data_loss on the old server. I lost 3 records, which we deemed acceptable. So I ran it on the production database last night with the same result. Checkdb runs without errors now.I'm not a db admin, obviously to my questions to this point, is it possible to run CHECKDB as part of a maintenance schedule? Backing up a corrupt database doesn't seem too useful... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-24 : 03:00:44
|
You should. It's in the integrity section of maintenance plans. Once a week is usually often enough.What is your backup strategy?quote:
I'm not a db admin
 Neither am I.--Gail ShawSQL Server MVP |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-24 : 04:10:02
|
You're blessed that only three records are gone.Can you compare the new table' content with an old table and maybe figure out which three records are missing?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
wiftody
Starting Member
5 Posts |
Posted - 2008-07-24 : 12:22:01
|
| GilaMonster:On our old server, we just backed it up as part of the drive backup. Currently that's how it's setup on the new server as well. Unfortunately, because of this way of backing up, there apparently is no integrity checking, so until we see application errors there is no way of knowing that data is corrupt. I guess I'll familiarize myself with the SQL Maintenance plans section.Peso:Good Idea. I restored the corrupt database and took a look for the missing records. I was able to find 1 record and restore it. One (or perhaps more) of the image files associated with it was corrupt. A maintenance program we run replaces the image files as needed, so nothing was really lost from that record. The other 2 records must be after the restore point I used (I restored the records to the old server). I'll see if I can find them this weekend. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-25 : 03:24:22
|
quote:
Originally posted by wiftody
GilaMonster:On our old server, we just backed it up as part of the drive backup. Currently that's how it's setup on the new server as well.
Do you mean that you're backing the mdf and ldf files to taps/backup device? If so, that's a really bad idea and asking for db problems.Integrity checks are a seperate step from backups in the maint plans. You can set up a maint plan tht just backs up, just does the integrity check or you can set up one that does both.--Gail ShawSQL Server MVP |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-25 : 09:23:43
|
| Use SQL Server to perform true database and log backups to disk. Then use your backup software to archive these files to archival storage.e4 d5 xd5 Nf6 |
|
|
|
drdesouza
Starting Member
6 Posts |
Posted - 2013-03-29 : 13:58:40
|
| I think all types of corruption related errors solved by this SQL Server Database Recovery Software as I have also used this application and solved my entire errors in SQL Server database very easily without damaging original data integrity. If you have any questions regarding these issues please follow this link: unspammed |
|
|
|
|
consistency errors -CHECKDB 8928, 8939, 8965, 8929