Backup Log Incorrect Function Message (nonrecoverable I/O error).

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Backup Log Incorrect Function Message...

Backup Log Incorrect Function Message (nonrecoverable I/O error).

Rate Topic

Display Mode

Topic Options

Author

Message

DB_Newbie2007

Posted Wednesday, January 02, 2013 7:07 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:47 AM
Points: 179, Visits: 1,086

We started receiving this message mid-last week:

Msg 3271, Level 16, State 1, Line 4
A nonrecoverable I/O error occurred on file "E:\SQLLogs\MyDB\MyDB.ldf:" 1(Incorrect function.).
Msg 3013, Level 16, State 1, Line 4
BACKUP LOG is terminating abnormally.

Appears to only happen on this one log file (all other logs files, ones on this disk as well as other disks backup fine. We are using the built-in SQL Backup for the log files (done to both a removable USB drive as well as an attached storage array),

BACKUP LOG [MyDB] TO DISK = 'M:\Backups\DataCap\MyDB_log_20130102.bak' WITH RETAINDAYS = 7, INIT

I also tried RedGate's Hyperbak, but still received the same error.

What has been done so far:
1) Try DBCC CHECKDISK (ran over 8 hours), no errors found.
2) Try back to different drive (attached). Still receive same error.
3) Full backup works fine:
12/30/2012 05:02:01,Backup,Unknown,Database backed up. Database: MyDB<c/> creation date(time): 2009/12/03(06:27:23)<c/> pages dumped: 109914737<c/> first LSN: 31680:46026:73<c/> last LSN: 31680:46057:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'M:\Backups\DataCap\MyDB_backup_20121230.hbc'}). This is an informational message only. No user action is required.

Server configuration:
Microsoft Server Enterprise 2008 (32-bit)
Service Pack 2

Intel Xeon CPU
E5470 @ 2.23 Ghz
3.33 Ghz, 32.0 GB of RAM

Server Server 2005 (32-bit)
Standard Edition
Service Pack 3

This particular log is 187GB in size, but is not the largest log by far (my largest on the same server is 818GB and it backs up fine).

I have tried to google the message above, but there appears to be little documentation on this particular error.

Nothing other than data inserts is being done on this particular database (no DML).

Any ideas or suggestions?

Argue your limitations and sure enough they will be yours (Richard Bach, Illusions)

Post #1401824

opc.three

Posted Wednesday, January 02, 2013 10:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 6,729, Visits: 11,776

Did you mean DBCC CHECKDB or the operating system routine CHKDSK?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1401945

DB_Newbie2007

Posted Wednesday, January 02, 2013 10:26 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:47 AM
Points: 179, Visits: 1,086

LOL. Sorry... problem with being at work with a cold? Not enough coffee yet? Actually, I ran both DBCC CheckDB (last week) and CHKDSK (today), no errors with either.

I found an article, http://social.technet.microsoft.com/Forums/en-US/windowsbackup/thread/5e882fba-1e97-43c1-bfc1-9d769165da6f
so I tried shrinking the disk with the log file, then re-tried the log backup. Still the same error message.

At this point I am re-running a full backup. Since I am grasping at straws here, I was thinking about a move of the log file to a different disk to see what happens.

Argue your limitations and sure enough they will be yours (Richard Bach, Illusions)

Post #1401956

opc.three

Posted Wednesday, January 02, 2013 10:49 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 6,729, Visits: 11,776

DB_Newbie2007 (1/2/2013)

Anything interesting in the Windows Event logs?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1401969

jasona.work

Posted Wednesday, January 02, 2013 11:16 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:02 AM
Points: 469, Visits: 2,364

I'm wondering, in looking at your two statements, the error message:

A nonrecoverable I/O error occurred on file "E:\SQLLogs\MyDB\MyDB.ldf:" 1(Incorrect function.).

Then your backup code:

BACKUP LOG [MyDB] TO DISK = 'M:\Backups\DataCap\MyDB_log_20130102.bak' WITH RETAINDAYS = 7, INIT

If possibly your log may be on a bad sector of the HD? Maybe, if possible, try moving the log file (temporarily) to another location, ideally another drive entirely (as I would suspect the OS would just change the pointer in the file tables on the disk to point to the new location, leaving it on the bad sector.)

Of course, the trick is both getting the time to do this, and then doing it. Although also, chkdsk *should* have found bad sectors when you ran it (at least reported them, I would NOT tell it to fix them without having backups of everything on the disk, it could end up being like running dbcc checkdb REPAIR_ALLOW_DATA_LOSS...)

We would occasionally run into the "Incorrect Function" problem when we had backups (not SQL) going to CD-RWs and the drive or media was going bad.

Also, as opc.three asked, what about in the Windows Event Logs around the time you tried taking the backup? Especially the System log.

Jason

Post #1401987

GilaMonster

Posted Wednesday, January 02, 2013 11:44 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 37,738, Visits: 30,008

CheckDB doesn't check the log file, so it wouldn't pick up the corruption.

Usual solution given for this case:
Restore a backup, full, and logs

Or, if you don't have backups (or can't afford to lose the data since the last log backup)
Switch to simple recovery model
Truncate the log (checkpoint)
Shrink the log
Grow it again (perhaps after moving to a new storage array)
Switch back to full recovery
Take a full backup
Restart log backups

The second 'solution' may not work. The first is recommended.

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 #1402008

DB_Newbie2007

Posted Wednesday, January 02, 2013 2:04 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:47 AM
Points: 179, Visits: 1,086

Gail,

Thanks for the response! I am worried that a restore may be in order, but that usually takes 2-3 days, due to the size of the database (853 GB for the mdf file, 187 GB for the ldf). Full backups are done weekly and log backups daily.

I did try to copy the ldf to a different drive (after doing another full backup). The copy was almost complete, then I received an "Invalid MS-DOS function" error message. *SIGH*

At this point, looks like a restore will be in order (tomorrow, when my head clears more!).

FYI, there were no errors in the event logs, other than the application error about the backup failing.

Hey... on the plus side.... this is the first time we have an this type of error! :)

Argue your limitations and sure enough they will be yours (Richard Bach, Illusions)

Post #1402073

GilaMonster

Posted Wednesday, January 02, 2013 3:45 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 37,738, Visits: 30,008

DB_Newbie2007 (1/2/2013)

Full backups are done weekly and log backups daily.

Daily? So up to 24 hours of data loss is acceptable?

Log backups are usually a lot more frequent than that, most backup strategies I've seen for up to 24 hours acceptable data loss use full and diff backups and simple recovery.

p.s. A 1 TB database shouldn't take 2-3 days to restore.

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 #1402103

DB_Newbie2007

Posted Monday, January 07, 2013 1:54 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:47 AM
Points: 179, Visits: 1,086

Ok... I was finally able to jump back on this problem and get it resolved:

1) Did a full backup this AM.
2) Detached the database.
3) Renamed the LDF file to ".old".
4) Re-attached the database with no log file (new one was created).
5) Moved the log to a different disk, re-sized it to something much larger.

We then shut the system down (needed MS updates anyway), ran a chkdsk /f at reboot and no problems were found on the drive! Go figure! Last week, every day was a problem day... this week we are correcting issues and the corrections/fixes are "sticking".

Regarding the question about backup(s) and frequency, No... 24 hour data loss is not acceptable (IMO) but it *is* tolerable. In this case, this server (and the databases on it) only receive data once a day. They can be millions of records, but still only once a day. If necessary, the automated processes can be re-started to re-process the data. Unlike the customer-facing server(s), this system actually would have the capacity to run backups on a more frequent basis. But hey... it took 3+ years just to get Mgmt to purchase software to compress and speed up the backup(s), never mind the question of where do we put them (capacity)! ;)

Argue your limitations and sure enough they will be yours (Richard Bach, Illusions)

Post #1403867

GilaMonster

Posted Monday, January 07, 2013 2:55 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 37,738, Visits: 30,008

Just a comment on your 'solution', you got lucky there, SQL cannot always recreate the log file if it's lost. It's very possible that deleting the log file will result in the DB being unusable and a restore from backup being needed.

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 #1403885

« Prev Topic | Next Topic »

Permissions