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

RESTORE DATABASE FAILS with Write on "???" failed: 1117(The request could not be performed because of an I/O device error.) Expand / Collapse
Author
Message
Posted Tuesday, July 3, 2012 10:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:38 PM
Points: 1,336, Visits: 741
I am currently fighting an issue in which I'm looking for confirmation that this could be related to a bad backup or not.

I have attempting to restore two seperate full backs for the same database.

What I am seeing is the following error during the process in the sql logs:
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [I:\database_name.mdf] in database [<Database name is not available>] (5). The OS file handle is 0x00000000000007EC. The offset of the latest long I/O is: 0x00001adda30000

Then the process a little later fails with:
Msg 3202, Level 16, State 1, Line 1
Write on "???" failed: 1117(The request could not be performed because of an I/O device error.)
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The system is virtual. One thing we have attempted to do is to remove the vdmk and recreated it in the same datastore and lun on the san within a share raid group.

Backups are being performed by the standard backup maintenance plan. Compression is enabled and verfiy backup integrity is enabled.

The server is running OS Windows Server 2008 R2 and SQL Server 2008 R2 SP1.

I believe this is being cause due to an I/O issue with the disk subsystem. However, could this be cause due to a bad backup?
Post #1324497
Posted Tuesday, July 3, 2012 10:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:53 PM
Points: 33,063, Visits: 15,179
Don't think so. The first error is an I/O subsystem error, surfaced when the I/O system doesn't respond correctly.

The backup error would surface differently as a failure to read from offset xxxx in the backup file.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1324514
Posted Tuesday, July 3, 2012 10:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:38 PM
Points: 1,336, Visits: 741
Thanks Steve. Just wanted to make sure we were spinning the cycles in the right location.
Post #1324524
Posted Tuesday, July 3, 2012 4:55 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:38 PM
Points: 1,336, Visits: 741
I just wanted to post a followup on this.

We ran three test to the backup

1. Restored to a Physical Server
2. Restored to a another virtual server using nas instead of San
3. Restored to the same virtual server after creating a new lun, datastore and virtual disk.

The compress backup we were restoring was 33 Gb to expand to a 140 gb mdf and 17 gb ldf

1. Took about 40 minutes to complete without any timeout messages
2. Took 4 Hours 30 minutes without any timeout messages
3. Took about 55 minutes with the same timeout message at about 40% complete

So with this I think it was safe to confirm that the issue was related to the drive I was restoring to and not the backup.

Again, Steve thanks for confirming where to focus the efforts.
Post #1324735
Posted Tuesday, July 3, 2012 5:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:53 PM
Points: 33,063, Visits: 15,179
You are welcome, and glad things are working for you.

Steve







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1324736
Posted Wednesday, July 4, 2012 5:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:08 AM
Points: 1,079, Visits: 591
Also has more or less similar problem where the issue was not even the backup but the log file.
We couldn';t backup the log file and it was increasing at least by 25GB a day.

Couldn't do a log back up but a full DB backup would backup succesful.

When doing a log backup I'd get error below:-

Msg 3203, Level 16, State 1, Line 1
Read on "L:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\FILENAME_log.ldf" failed: 1117(failed to retrieve
text for this error. Reason: 15105) Msg 3013, Level 16, State 1, Line 1BACKUP LOG is terminating abnormally.

This turned out to be something wrong with the disk where the log file was placed.


What you don't know won't hurt you but what you know will make you plan to know better
Post #1324950
Posted Tuesday, July 10, 2012 2:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:10 AM
Points: 112, Visits: 1,207
The error arises when filemark in the backup device could not be read. There are many reasons why you may encounter a filemark error. Some of the reasons include the following :

A media failure may occur on the device where the backup is located.
A write failure may occur during the creation of the backup.

To allow SQL Server to perform new backups to the backup device, you must manually delete or erase the device by using the following command:

BACKUP DATABASE mydatabase TO DISK='C:\MyDatabase.bak' with FORMAT
For more information about error solution, please check this link : http://support.microsoft.com/kb/290787


SQL Database Recovery Expert
Post #1327329
Posted Tuesday, July 10, 2012 2:39 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:10 AM
Points: 112, Visits: 1,207
BudaCli (7/4/2012)
Also has more or less similar problem where the issue was not even the backup but the log file.
We couldn';t backup the log file and it was increasing at least by 25GB a day.

Couldn't do a log back up but a full DB backup would backup succesful.

When doing a log backup I'd get error below:-

Msg 3203, Level 16, State 1, Line 1
Read on "L:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\FILENAME_log.ldf" failed: 1117(failed to retrieve
text for this error. Reason: 15105) Msg 3013, Level 16, State 1, Line 1BACKUP LOG is terminating abnormally.

This turned out to be something wrong with the disk where the log file was placed.


There are many reasons of large transaction log file size which you can easily find out from here: http://www.recoverdatabase.blogspot.in/2012/04/what-to-do-if-sql-server-log-file.html

Before you try to back up the transaction log for a database in Microsoft SQL Server, you must perform a full database backup. Otherwise, you receive this error message that indicate that the BACKUP LOG statement stops abnormally. You must also perform a full database backup before you try to back up the transaction log for a database in Microsoft SQL Server 2000. However, if you do not perform a full database backup before you back up the transaction log of a database in SQL Server 2000, the BACKUP LOG statement does not stop. For more information about the error, please check this link: http://support.microsoft.com/kb/928317


SQL Database Recovery Expert
Post #1327336
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse