October 28, 2011 at 1:41 pm
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.
October 28, 2011 at 2:10 pm
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.
October 28, 2011 at 2:29 pm
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.
October 29, 2011 at 4:28 am
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
October 31, 2011 at 6:49 am
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.
October 31, 2011 at 7:16 am
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.
October 31, 2011 at 8:52 am
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
April 24, 2013 at 8:42 am
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
April 24, 2013 at 11:23 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2013 at 12:29 pm
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?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply