Help: Non recoverable I/O error

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Help: Non recoverable I/O error

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2006-08-25 : 11:00:16

Hi there

When I do a backup one of our database, I got the following error:

Non recoverable I/O error occured on file 'D:\MSSQL\xxx.mdf'. Backup Database is terminating abnormally.

It seem that the disk got a bad sector.

Surprisingly ... the application that use this db is still running.

Any idea to make this backup happening.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-25 : 13:02:43

Does DBCC CHECKDB return errors? If so, then you've got database corruption due to your hardware problems.

Tara Kizer

dewacorp.alliances

452 Posts

Posted - 2006-08-25 : 18:52:55

Hi there

I ran DBCC CHECKDB on that database and returns the message below.

My question is if I run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS is this going to loss any data?

Thanks

I only pick which the one that have an error:

===================================================

Server: Msg 8966, Level 16, State 2, Line 1
Could not read and latch page (1:1261) with latch type UP. 23(error not found) failed.
DBCC results for 'db01210101_2'.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for 'sysobjects'.
There are 1438 rows in 24 pages for object 'sysobjects'.
DBCC results for 'CAT_TypesTexts'.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15553789952 owned by data record identified by RID = (1:1632:0) ScheduleHistoryID = 11632.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15579480064 owned by data record identified by RID = (1:3488:1) ScheduleHistoryID = 11711.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15605825536 owned by data record identified by RID = (1:3489:0) ScheduleHistoryID = 11808.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15678046208 owned by data record identified by RID = (1:3490:30) ScheduleHistoryID = 12078.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15678570496 owned by data record identified by RID = (1:3490:32) ScheduleHistoryID = 12080.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15680405504 owned by data record identified by RID = (1:3490:39) ScheduleHistoryID = 12087.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15680929792 owned by data record identified by RID = (1:3490:41) ScheduleHistoryID = 12089.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15682502656 owned by data record identified by RID = (1:3491:3) ScheduleHistoryID = 12095.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15682764800 owned by data record identified by RID = (1:3491:4) ScheduleHistoryID = 12096.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15683026944 owned by data record identified by RID = (1:3491:5) ScheduleHistoryID = 12097.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15684075520 owned by data record identified by RID = (1:3491:9) ScheduleHistoryID = 12101.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15685124096 owned by data record identified by RID = (1:3491:13) ScheduleHistoryID = 12105.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15685713920 owned by data record identified by RID = (1:3491:16) ScheduleHistoryID = 12108.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15685976064 owned by data record identified by RID = (1:3491:17) ScheduleHistoryID = 12109.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15687155712 owned by data record identified by RID = (1:3491:23) ScheduleHistoryID = 12115.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15689842688 owned by data record identified by RID = (1:3491:27) ScheduleHistoryID = 12119.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15691415552 owned by data record identified by RID = (1:3491:32) ScheduleHistoryID = 12124.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15693381632 owned by data record identified by RID = (1:3491:39) ScheduleHistoryID = 12131.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15696527360 owned by data record identified by RID = (1:3491:51) ScheduleHistoryID = 12143.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15697182720 owned by data record identified by RID = (1:3491:54) ScheduleHistoryID = 12146.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15700983808 owned by data record identified by RID = (1:3491:69) ScheduleHistoryID = 12161.
Server: Msg 2533, Level 16, State 1, Line 1
Table error: Page (1:1261) allocated to object ID 693577509, index ID 255 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 0, text ID 15685713920 is referenced by page (1:3491), slot 16, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 1, text ID 15687155712 is referenced by page (1:3491), slot 23, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 2, text ID 15553789952 is referenced by page (1:1632), slot 0, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 3, text ID 15579480064 is referenced by page (1:3488), slot 1, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 4, text ID 15605825536 is referenced by page (1:3489), slot 0, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 5, text ID 15685976064 is referenced by page (1:3491), slot 17, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 6, text ID 15689842688 is referenced by page (1:3491), slot 27, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 7, text ID 15691415552 is referenced by page (1:3491), slot 32, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 8, text ID 15693381632 is referenced by page (1:3491), slot 39, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 9, text ID 15696527360 is referenced by page (1:3491), slot 51, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 10, text ID 15697182720 is referenced by page (1:3491), slot 54, but was not seen in the scan.
DBCC results for 'ScheduleHistory'.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 11, text ID 15700983808 is referenced by page (1:3491), slot 69, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 13, text ID 15678046208 is referenced by page (1:3490), slot 30, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 14, text ID 15678570496 is referenced by page (1:3490), slot 32, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 15, text ID 15680405504 is referenced by page (1:3490), slot 39, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 16, text ID 15680929792 is referenced by page (1:3490), slot 41, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 17, text ID 15682502656 is referenced by page (1:3491), slot 3, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 18, text ID 15682764800 is referenced by page (1:3491), slot 4, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 19, text ID 15683026944 is referenced by page (1:3491), slot 5, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 29, text ID 15684075520 is referenced by page (1:3491), slot 9, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 30, text ID 15685124096 is referenced by page (1:3491), slot 13, but was not seen in the scan.
There are 5619 rows in 75 pages for object 'ScheduleHistory'.
CHECKDB found 0 allocation errors and 43 consistency errors in table 'ScheduleHistory' (object ID 693577509).

CHECKDB found 0 allocation errors and 44 consistency errors in database 'db01210101_2'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (db01210101_2 ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-25 : 18:57:57

quote:
Originally posted by valdyv

My question is if I run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS is this going to loss any data?

Yes, potentially at least. That's the point of that command. Post your DBCC output in the Data Corruption forum here so that MS engineers can help you resolve the data problems. You'll still need to resolve your hardware problems though as this problem will probably occur again if you don't.

Tara Kizer

dewacorp.alliances

452 Posts

Posted - 2006-08-25 : 19:16:34

Due to the backup is no good (lucky this is not a production but still there is a bit work at app level to build this), if I do detach the database and copy the MDF and LDF somewhere and do the re-attach back to second server, will it a good idea to cover this by doing the DBCC CHECKDB repair_allow_data_loss on the second server perhaps? My concern is if I detach back on the first server, will it be back to retach again on first server?

I'm appreciate your feedback.

dewacorp.alliances

452 Posts

Posted - 2006-08-27 : 00:49:38

Update:

Try the following:
1. Shutdown the MSSQL service and copy mdf and ldf and the result is unsuccessfull.
2. Run Check Disk and it's a complete. I thought this will recover and try do do a backup still the error comes which is: "Non recoverable I/O error occured on file xxx.mdf. Backup database is terminating abnormally."
3. Run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS and then REPAIR_FAST and it did fix and no consistency error any more. So I thought this will fixed ... so I run backup once again and it comes up the same error (2).

I am running out an option here. Any ideas?

Thanks

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-28 : 16:03:46

valldyv,

quote:

I am running out an option here. Any ideas?

Ummm... Off the top of my head, you might try posting to the Database Corruption forum like Tara suggested two messages earlier than your last one.

Ken

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 01:33:06

Cross post.
Also answered here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71094

Peter Larsson
Helsingborg, Sweden

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-29 : 07:46:34

quote:
Originally posted by Peso

Cross post.
Also answered here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71094

Peter Larsson
Helsingborg, Sweden

not really a cross post since it was requested that he post in that forum. btw, paul randal from Microsoft monitors the data corruption forum. Any and all data corruption questions should go over there since he knows this area inside and out.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources