LOB Corruption - SQL Server Forums

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

LOB Corruption

Author

Topic

lightner
Starting Member

1 Post

Posted - 2011-03-02 : 18:35:42

I need some help with LOB corruption. I have a web front end that uses SQL 2005 (sp4) backend 2008 server. Starting in November of 2010 I started getting corruption in the tables that contain LOB data types. It happens in 8 different databases all at the same time. Each one of the databases structures are identical. The LOB data is accessed multiple times a day, so I know it when it right away when someone reads the data. All my research pointed to I/O subsystem problems so last weekend I moved all the databases to a new server. (These are dedicated servers hosted at a large hosting facility. The old server was 2003) Today the same thing happened. I am at a loss! I can run dbcc with data loss to repair, but all my clients have to go in and upload the missing images. Here is a snippet of the dbcc checkdb:
Msg 8929, Level 16, State 1, Line 1
Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042908672 (type In-row data): Errors found in off-row data with ID 267321344 owned by data record identified by RID = (1:222:5)
Msg 8929, Level 16, State 1, Line 1
Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042908672 (type In-row data): Errors found in off-row data with ID 401866752 owned by data record identified by RID = (1:222:41)
Msg 8929, Level 16, State 1, Line 1
Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042908672 (type In-row data): Errors found in off-row data with ID 407568384 owned by data record identified by RID = (1:630:31)
Msg 8929, Level 16, State 1, Line 1
Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042908672 (type In-row data): Errors found in off-row data with ID 790626304 owned by data record identified by RID = (1:630:86)
Msg 8929, Level 16, State 1, Line 1
Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042908672 (type In-row data): Errors found in off-row data with ID 800849920 owned by data record identified by RID = (1:1582:29)
Msg 8929, Level 16, State 1, Line 1
Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042908672 (type In-row data): Errors found in off-row data with ID 924254208 owned by data record identified by RID = (1:5957:20)
Msg 8928, Level 16, State 1, Line 1
Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data): Page (1:1000) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data), page (1:1000). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data). The off-row data node at page (1:1000), slot 0, text ID 267321344 is referenced by page (1:969), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data): Page (1:2362) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data), page (1:2362). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data). The off-row data node at page (1:2362), slot 0, text ID 401866752 is referenced by page (1:2346), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data): Page (1:2363) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data), page (1:2363). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data). The off-row data node at page (1:2363), slot 0, text ID 401866752 is referenced by page (1:2346), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data): Page (1:2391) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data), page (1:2391). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data). The off-row data node at page (1:2391), slot 0, text ID 401866752 is referenced by page (1:2346), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data): Page (1:4634) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 309576141, index ID 0, partition ID 72057594038714368, alloc unit ID 72057594042974208 (type LOB data), page (1:4634). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1

I just set up the maintenance plan to run database and transaction log backups last night. Everything seem fine until then. The strange thing is I used to see 3-10 inconsistancies, but this morning some of the databases reported up to 1000. This is bad!
Any help would greatly be appreciated.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-03 : 01:59:13

I suppose there's no point in asking if you have a clean backup?

Please run the following and post the full and complete output. If you have over 1000 errors you will need to run this from SQLCMD rather than management studio, direct the output to a text file.

DBCC CheckDB(<Database name>) WITH No_INFOMSGS, ALL_ERRORMSGS

Check the system event logs, RAID driver logs, HBA logs, SAN logs, etc. Repeated corruption is almost certainly IO subsystem.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources