I/O torn page detected during read

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

I/O torn page detected during read

Author

Topic

shallu
Starting Member

2 Posts

Posted - 2009-03-17 : 11:22:52

Hi All,

I am getting the following error when I try to run select * from _instance

Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000131f2000 in file 'D:\XceleraDatabase\MSSQL$XCELERAINST\XceleraData.mdf'.

Connection Broken

I ran DBCC CHECKDB and got the following output

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 2, page ID (1:76360). The PageId in the page header = (0:0).
DBCC results for 'Xcelera'.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for 'sysobjects'.
There are 614 rows in 11 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 229 rows in 9 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 2023 rows in 74 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 33 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 576 rows in 171 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 381 rows in 2 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 51 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 3173 rows in 22 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 39 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for '_PHYSICIAN_PATIENT_LIST'.
There are 23848 rows in 51 pages for object '_PHYSICIAN_PATIENT_LIST'.
DBCC results for '_HARMONY_ROLES'.
There are 30 rows in 1 pages for object '_HARMONY_ROLES'.
DBCC results for '_HARMONY_ROLES_LABEL'.
There are 240 rows in 9 pages for object '_HARMONY_ROLES_LABEL'.
DBCC results for '_XA_INSTALL_HISTORY'.
There are 18 rows in 1 pages for object '_XA_INSTALL_HISTORY'.
DBCC results for '_ROLE_SET'.
There are 4 rows in 1 pages for object '_ROLE_SET'.
DBCC results for '_XA_STRING_TABLE'.
There are 8 rows in 1 pages for object '_XA_STRING_TABLE'.
DBCC results for '_ROLE_SET_LABEL'.
There are 32 rows in 1 pages for object '_ROLE_SET_LABEL'.
DBCC results for '_ORDER_STATUS'.
There are 1 rows in 1 pages for object '_ORDER_STATUS'.
DBCC results for '_IMAGE_FORMAT'.
There are 5 rows in 1 pages for object '_IMAGE_FORMAT'.
DBCC results for '_ROLE_ASSIGNMENTS'.
There are 71 rows in 1 pages for object '_ROLE_ASSIGNMENTS'.
DBCC results for '_LANGUAGE'.
There are 136 rows in 2 pages for object '_LANGUAGE'.
DBCC results for '_GENDER'.
There are 4 rows in 1 pages for object '_GENDER'.
DBCC results for '_PREFETCH_REQUEST'.
There are 0 rows in 1 pages for object '_PREFETCH_REQUEST'.
DBCC results for '_GENDER_LABEL'.
There are 32 rows in 1 pages for object '_GENDER_LABEL'.
DBCC results for '_ANONYMOUS_TAGS'.
There are 0 rows in 0 pages for object '_ANONYMOUS_TAGS'.
DBCC results for '_SEARCH_INFO'.
There are 44876 rows in 5079 pages for object '_SEARCH_INFO'.
DBCC results for '_STUDY_STATUS'.
There are 5 rows in 1 pages for object '_STUDY_STATUS'.
DBCC results for '_STATUS_LABEL'.
There are 40 rows in 1 pages for object '_STATUS_LABEL'.
DBCC results for '_LOCATION'.
There are 21 rows in 1 pages for object '_LOCATION'.
DBCC results for '_PATIENT'.
There are 23848 rows in 1507 pages for object '_PATIENT'.
DBCC results for '_STUDY'.
There are 44880 rows in 500 pages for object '_STUDY'.
DBCC results for '_STUDY_INSTANCE'.
There are 45099 rows in 2280 pages for object '_STUDY_INSTANCE'.
DBCC results for '_STORAGE_LOC'.
There are 5 rows in 1 pages for object '_STORAGE_LOC'.
DBCC results for '_STORAGE_SOLUTION'.
There are 2 rows in 1 pages for object '_STORAGE_SOLUTION'.
DBCC results for '_COMPRESSION'.
There are 3 rows in 1 pages for object '_COMPRESSION'.
DBCC results for '_STATE'.
There are 6 rows in 1 pages for object '_STATE'.
DBCC results for '_STUDY_INSTANCE_STORAGE'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1397580017, index ID 0: Page (1:39161) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1397580017, index ID 0, page (1:39161). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1397580017, index ID 0: Page (1:76360) could not be processed. See other errors for details.
There are 56909 rows in 3547 pages for object '_STUDY_INSTANCE_STORAGE'.
DBCC results for '_SERIES'.
There are 47525 rows in 3480 pages for object '_SERIES'.
DBCC results for '_INSTANCE'.
There are 1645109 rows in 189324 pages for object '_INSTANCE'.
CHECKDB found 0 allocation errors and 3 consistency errors in table '_INSTANCE' (object ID 1397580017).
DBCC results for '_PERF_LOG'.
There are 516398 rows in 22453 pages for object '_PERF_LOG'.
DBCC results for '_INSTITUTION'.
There are 1 rows in 1 pages for object '_INSTITUTION'.
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object 'dtproperties'.
DBCC results for '_PATIENT_MRN'.
There are 23848 rows in 513 pages for object '_PATIENT_MRN'.
DBCC results for '_RACE'.
There are 2 rows in 1 pages for object '_RACE'.
DBCC results for '_PATIENT_RACE'.
There are 9 rows in 1 pages for object '_PATIENT_RACE'.
DBCC results for '_MEDICAL_HISTORY'.
There are 101 rows in 1 pages for object '_MEDICAL_HISTORY'.
DBCC results for '_PATIENT_MEDICAL_HISTORY'.
There are 3399 rows in 22 pages for object '_PATIENT_MEDICAL_HISTORY'.
DBCC results for '_ENTERED_PATIENT'.
There are 44875 rows in 3088 pages for object '_ENTERED_PATIENT'.
DBCC results for '_CLINICAL_GROUP'.
There are 5 rows in 1 pages for object '_CLINICAL_GROUP'.
DBCC results for '_VIEWER_UPDATE'.
There are 1 rows in 1 pages for object '_VIEWER_UPDATE'.
DBCC results for '_XA_CONFIGURATION'.
There are 1 rows in 1 pages for object '_XA_CONFIGURATION'.
DBCC results for '_CLINICIAN'.
There are 390 rows in 11 pages for object '_CLINICIAN'.
DBCC results for '_INSTANCE_REFERENCE'.
There are 0 rows in 0 pages for object '_INSTANCE_REFERENCE'.
DBCC results for '_CLINICAL_GROUP_MEMBERS'.
There are 677 rows in 2 pages for object '_CLINICAL_GROUP_MEMBERS'.
CHECKDB found 0 allocation errors and 4 consistency errors in database 'Xcelera'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Xcelera ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I ran dbcc checktable('_INSTANCE') and got the following output
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 2, page ID (1:76360). The PageId in the page header = (0:0).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1397580017, index ID 0: Page (1:39161) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1397580017, index ID 0, page (1:39161). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1397580017, index ID 0: Page (1:76360) could not be processed. See other errors for details.
CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for '_INSTANCE'.
There are 1707709 rows in 196511 pages for object '_INSTANCE'.
CHECKTABLE found 0 allocation errors and 3 consistency errors in table '_INSTANCE' (object ID 1397580017).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (Xcelera.dbo._INSTANCE ).

Please help! I don't know how to fix it.
Shallu

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-03-17 : 11:53:12

with caution check if this helps: http://sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

shallu
Starting Member

2 Posts

Posted - 2009-03-17 : 12:05:01

Thank you Dinaker for the reply. But I need to know what I am losing before I run any repair or try the emergency mode for the DB.

BTW, I am using SQL Server 2000

I appreciate the help
Shallu

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-17 : 13:04:35

It's not going to tell you what you're losing. It makes those decisions as it goes through and tries to fix things. Look at it this way it's unusable right now. Usable with a slight loss is always a better option. I don't suppose you have a good set of backups and transaction logs you can just restore over this with?

Mike
"oh, that monkey is going to pay"

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-03-17 : 20:48:49

You don't need to use EMERGENCY mode repair - that's only when the transaction log is damaged.

Best way to recover is to use your backups. If you don't have backups, you'll need to use repair - which will deallocate the two corrupt pages and you'll lose the data on them.

Be sure to check the I/O subsystem to work out why this occured. A torn-page is caused by the hardware - maybe a power outage occured and one of the drives doesn't have sufficient battery-backup to successfully flush it's write cache?

Thanks

Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)
SQL Server MVP, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources