Urgent - Consistency Errors after running CHECKDB

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.

All Forums

General SQL Server Forums

Data Corruption Issues

Urgent - Consistency Errors after running CHECKDB

Author

Topic

Arjela
Starting Member

5 Posts

Posted - 2010-12-14 : 06:47:04

Our Production DB on SQL Server 2008 SP1 (x64) reported some errors this morning

I have run CHECKDB with the following results.

Msg 8929, Level 16, State 1, Line 1
Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 281474980642816 (type In-row data): Errors found in off-row data with ID 1411842048 owned by data record identified by RID = (1:3717:1)
Msg 8928, Level 16, State 1, Line 1
Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data): Page (1:73173) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data), page (1:73173). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -1.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). The off-row data node at page (1:73173), slot 0, text ID 1411842048 is referenced by page (1:22265), slot 0, but was not seen in the scan.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'sys.sysobjvalues' (object ID 60).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'my db'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (my db)

I really need some help from somebody who can help me understand what these errors are telling me and how I can correct them with minimum data loss.

So far I have:
* Prevented any further access to the db by stopping users from accessing the application that connect to the db
* Run checkDB

Thanks for any help

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-14 : 07:02:36

One of the pages of a system table has damaged LOB pages.

Do you have a clean backup from before this started? Are you running full recovery with log backups?

--
Gail Shaw
SQL Server MVP

Arjela
Starting Member

5 Posts

Posted - 2010-12-14 : 07:16:55

Yes the error occured at 01:18 this morning and I have a backup to 19:00 yesterday with 15 minute trans logs backups so I can restore to to 01:00 or 01:15

The 01:00 log was 270,000 (its usual size is between 200,00 and 700,000)
The 01:15 log was 9,213,952

I therefore think that is safer to restore to 01:00 than to 01:15

Do you agree?

Arjela
Starting Member

5 Posts

Posted - 2010-12-14 : 07:43:47

Hi Gail, Thanks for your help. I restored the DB and have run checkDB again with no errors so the application is now operational.

However, the million dollar question is How can I work out the cause of the problem and what can i do to avoid this in the future?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-14 : 07:59:27

Check the Windows event logs on that server for anything that might indicate hardware problems. You could have a bad disk or controller. If there are diagnostics for your disk hardware, run them. If there is a hardware issue then you could encounter more corruption.

Make sure you have copies of your backups on another machine too.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-14 : 08:03:22

IO subsystem problems. Something modified a page outside of SQL Server.

--
Gail Shaw
SQL Server MVP

Arjela
Starting Member

5 Posts

Posted - 2010-12-14 : 08:05:35

Thanks

I have Run HP diagnostics on the Disks and they are reporting to be fine

The application log reported:
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xaaaaaa9a). It occurred during a read of page (1:73173) in database ID 5 at offset 0x00000023baa000 in file '\my db.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.

There is nothing in the system log

The sql server log reported:
12/14/2010 01:18:44,spid57,Unknown,SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xaaaaaa9a). It occurred during a read of page (1:73173) in database ID 5 at offset 0x00000023baa000 in file 'my db.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<c/> see SQL Server Books Online.
12/14/2010 01:18:44,spid57,Unknown,Error: 824<c/> Severity: 24<c/> State: 2.

Arjela
Starting Member

5 Posts

Posted - 2010-12-14 : 08:12:05

quote:
Originally posted by GilaMonster

IO subsystem problems. Something modified a page outside of SQL Server.

How is it possible to modify a page outside of SQL server?

Are you saying that an area of the disk used by a page may have been overwritten by another non SQL Server application?

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-14 : 08:38:22

Usually it's a faulty IO subsystem - misbehaving drivers, old firmware, faulty drives, etc. It's not typically another app. If that does happen, you really have problems if some app is writing over other files.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources