January 2, 2013 at 7:07 am
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 for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 2, 2013 at 10:07 am
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
January 2, 2013 at 10:26 am
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 for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 2, 2013 at 10:49 am
DB_Newbie2007 (1/2/2013)
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.
Anything interesting in the Windows Event logs?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 2, 2013 at 11:16 am
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
January 2, 2013 at 11:44 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2013 at 2:04 pm
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 for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 2, 2013 at 3:46 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2013 at 1:54 pm
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 for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 7, 2013 at 2:55 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply