Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

possible corruption due to i/o device error Expand / Collapse
Author
Message
Posted Friday, October 28, 2011 1:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 1:40 PM
Points: 201, Visits: 422
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.
Post #1197653
Posted Friday, October 28, 2011 2:10 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:24 AM
Points: 1,265, Visits: 3,598
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.
Post #1197663
Posted Friday, October 28, 2011 2:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 1:40 PM
Points: 201, Visits: 422
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.
Post #1197669
Posted Saturday, October 29, 2011 4:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1197726
Posted Monday, October 31, 2011 6:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 1:40 PM
Points: 201, Visits: 422
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.
Post #1198021
Posted Monday, October 31, 2011 7:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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.
Post #1198041
Posted Monday, October 31, 2011 8:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1198108
Posted Wednesday, April 24, 2013 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 5:20 AM
Points: 3, 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.
Failed-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
Post #1446024
Posted Wednesday, April 24, 2013 11:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
rajeshwaran2k6 (4/24/2013)

Failed-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 2008, MVP
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

Post #1446134
Posted Friday, April 26, 2013 12:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 5:20 AM
Points: 3, 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?
Post #1447123
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse