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


RESTORE DATABASE FAILS with Write on "???" failed: 1117(The request could not be performed because...


RESTORE DATABASE FAILS with Write on "???" failed: 1117(The request could not be performed because of an I/O device error.)

Author
Message
eccentricDBA
eccentricDBA
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2237 Visits: 1354
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?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)

Group: Administrators
Points: 149908 Visits: 19448
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
My Blog: www.voiceofthedba.com
eccentricDBA
eccentricDBA
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2237 Visits: 1354
Thanks Steve. Just wanted to make sure we were spinning the cycles in the right location.
eccentricDBA
eccentricDBA
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2237 Visits: 1354
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)

Group: Administrators
Points: 149908 Visits: 19448
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
My Blog: www.voiceofthedba.com
BudaCli
BudaCli
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1572 Visits: 598
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
prettsons
prettsons
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1917 Visits: 1411
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 :-)
prettsons
prettsons
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1917 Visits: 1411
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 :-)
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