SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Help: Non recoverable I/O error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts

Posted - 08/25/2006 :  11:00:16  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
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


Edited by - dewacorp.alliances on 08/25/2006 11:42:34

tkizer
Almighty SQL Goddess

USA
36685 Posts

Posted - 08/25/2006 :  13:02:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
Does DBCC CHECKDB return errors? If so, then you've got database corruption due to your hardware problems.

Tara Kizer

Edited by - tkizer on 08/25/2006 13:03:08
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts

Posted - 08/25/2006 :  18:52:55  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
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.

Edited by - dewacorp.alliances on 08/25/2006 19:27:46
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36685 Posts

Posted - 08/25/2006 :  18:57:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts

Posted - 08/25/2006 :  19:16:34  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
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.

Edited by - dewacorp.alliances on 08/25/2006 19:25:12
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts

Posted - 08/27/2006 :  00:49:38  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
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
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 08/28/2006 :  16:03:46  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30121 Posts

Posted - 08/29/2006 :  01:33:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Cross post.
Also answered here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71094


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/29/2006 :  07:46:34  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000