SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


possible corruption due to i/o device error


possible corruption due to i/o device error

Author
Message
Denise McMillan
Denise McMillan
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 501
I'm not sure if the database is corrupted but am having issues getting a backups to work. Error: The driver detected a controller error on \device\harddisk0\DR0.
In SQL log: The operating system returned error 1117(the request could not be performed because of an i/o device error) to SQL Server during a read at offset 0x....
And: Read on 'D:\...mdf' failed 1117(the request could not be performed because of an i/o device error)
Our server administrator found an error with the drive and swapped it out. I'm still haveing i/o errors for two of the databases though. Are they corrupt? How can I tell if they should be restored? I there a setting somewhere that would have to be set after the drive swap?
I ran dbcc checkdb on the 2 databases and they also have a 1117 error.
Any help or advice on this? Thanks very much.
calvo
calvo
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 4009
When a drive craps out on you, there's always a possibility a database can become corrupt. If you are able to hot-swap the drive successfully, there's nothing needed to be done from the database side.

I am no expert on corruption but the experts here would ask you to run this script and post the results for each database which you think might be affected.
DBCC CHECKDB('databaseName') WITH NO_INFOMSGS,ALL_ERRORMSGS


Whatever you do, do NOT run the checkdb command with any repair options! (not yet at least. not until an expert tells you to.)

They will also ask if you have good backups to restore to in the event there is no recovering from the disk failure.
Once done, they will be able to help you.

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Denise McMillan
Denise McMillan
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 501
Results of the dbcc checkdb:
for one database:

Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:16648) with latch type SH. 1117(The request could not be performed because of an I/O device error.) failed.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16648) allocated to object ID 1061578820, index ID 2, partition ID 72057594065387520, alloc unit ID 72057594066763776 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1061578820, index ID 2, partition ID 72057594065387520, alloc unit ID 72057594066763776 (type In-row data). Page (1:16648) was not seen in the scan although its parent (1:50739) and previous (1:16647) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1061578820, index ID 2, partition ID 72057594065387520, alloc unit ID 72057594066763776 (type In-row data). Page (1:16649) is missing a reference from previous page (1:16648). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'WAClickAggregationByDate' (object ID 1061578820).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'WebAnalyticsServiceApplication_ReportingDB_81a47478-1052-4bb2-a2b9-b1d481880451'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (WebAnalyticsServiceApplication_ReportingDB_81a47478-1052-4bb2-a2b9-b1d481880451).


for the second db:

Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:41200) with latch type SH. 1117(The request could not be performed because of an I/O device error.) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:390378) with latch type SH. 1117(The request could not be performed because of an I/O device error.) failed.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:41200) allocated to object ID 1099150961, index ID 1, partition ID 72057594052608000, alloc unit ID 72057594055753728 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1099150961, index ID 1, partition ID 72057594052608000, alloc unit ID 72057594055753728 (type In-row data). Page (1:41200) was not seen in the scan although its parent (1:477330) and previous (1:39815) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1099150961, index ID 1, partition ID 72057594052608000, alloc unit ID 72057594055753728 (type In-row data). Page (1:41201) is missing a reference from previous page (1:41200). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'FeatureUsage_Partition8' (object ID 1099150961).
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:390378) allocated to object ID 1342627826, index ID 1, partition ID 72057594048806912, alloc unit ID 72057594300465152 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1342627826, index ID 1, partition ID 72057594048806912, alloc unit ID 72057594300465152 (type In-row data). Page (1:390378) was not seen in the scan although its parent (1:390816) and previous (1:390377) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1342627826, index ID 1, partition ID 72057594048806912, alloc unit ID 72057594300465152 (type In-row data). Page (1:390379) is missing a reference from previous page (1:390378). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'RequestUsage_Partition11' (object ID 1342627826).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'WSS_Logging'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (WSS_Logging).


Again thanks for help with this.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88495 Visits: 45284
Do you have clean backups from before the crash?
Do you have a chain of log backups from that full up until now?

p.s. from the errors, the hardware is faulty. Moving the DBs to alternate storage should be a priority.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Denise McMillan
Denise McMillan
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 501
Yes, I have full backups from before the first error message and transcaction log backups. They have been working. The server adminstrators and our vendor say the drives are ok.
But I will work on moving these two databases. Thanks very much for the help.
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29351 Visits: 9671
Denise McMillan (10/31/2011)
Yes, I have full backups from before the first error message and transcaction log backups. They have been working. The server adminstrators and our vendor say the drives are ok.
But I will work on moving these two databases. Thanks very much for the help.



The ONLY way those drives are ok is if you've hit a bug is sql server. Not impossible but as likely as winning the big prize in a lottery.


You also need to check the link between "windows" and those drives. So cards, configs, usb ports, etc. I'm sure Gail as plenty of horror stories about those parts.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88495 Visits: 45284
Denise McMillan (10/31/2011)
The server adminstrators and our vendor say the drives are ok.


Those drives are not OK.

Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:41200) with latch type SH. 1117(The request could not be performed because of an I/O device error.) failed


Have a read through the windows system event log. My guess is you'll find IO-related errors from around the same time as the SQL DBs showed problems

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


rajeshwaran2k6
rajeshwaran2k6
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 8
Hi All,

I have similar case here. I cant able to take backup in my local drive G and error as I/O device error. I tried with different drives, but no luck. Thought It might be page corruption and used DBCC Checkdb which is fine. In event viewer, I can see 'Read on "G:\\SQL Database\\DBNAME.ldf" failed'. Please find the below error and any help at earliest will be much appreciated:-)

ERROR:

Maintenance Plan: DB Backup Differential Status
Duration: 00:17:36
Status: Warning: One or more tasks failed.
Details:
Back Up Database (Differential) (ts-server)
Backup Database on Local server connection
Databases: JLConcpetsProduction
Type: Differential
Append existing
Task start: 2013-04-24T04:00:01.
Task end: 2013-04-24T04:17:37.
FailedSad-1073548784) Executing the query "BACKUP DATABASE [JLConcpetsProduction] TO DISK = ..." failed with the following error: "Read on "G:\\SQL Database JLCONCEPTS\\JLConcpetsProduction.ldf" failed: 1117(The request could not be performed because of an I/O device error.)
BACKUP DATABASE is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
Processed 6101864 pages for database 'JLConcpetsProduction', file 'ISSIMODEL' on file 1.
60 percent processed.
70 percent processed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:BACKUP DATABASE [JLConcpetsProduction] TO DISK = N''G:\SQLSERVERJLCONPROD16032013\JLConcpetsProduction_backup_2013_04_24_040001_4095483.bak'' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N''JLConcpetsProduction_backup_2013_04_24_040001_4095483'', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''JLConcpetsProduction'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''JLConcpetsProduction'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''JLConcpetsProduction'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''G:\SQLSERVERJLCONPROD16032013\JLConcpetsProduction_backup_2013_04_24_040001_4095483.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88495 Visits: 45284
rajeshwaran2k6 (4/24/2013)

FailedSad-1073548784) Executing the query "BACKUP DATABASE [JLConcpetsProduction] TO DISK = ..." failed with the following error: "Read on "G:\\SQL Database JLCONCEPTS\\JLConcpetsProduction.ldf" failed: 1117(The request could not be performed because of an I/O device error.)


There's some problems with the G drive, speak with your storage or system admins to identify and resolve it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


rajeshwaran2k6
rajeshwaran2k6
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 8
Hi Gial,

Thanks for your quick response.Shall I try with moving the log file to another drive using detach and attach. Does it really helps me?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search