InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 06:11:58 GMT20http://www.sqlservercentral.com/Forums/Topic1401824-357-1.aspxJust 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.Mon, 07 Jan 2013 14:55:43 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401824-357-1.aspxOk... 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)! ;)Mon, 07 Jan 2013 13:54:28 GMTDB_Newbie2007http://www.sqlservercentral.com/Forums/Topic1401824-357-1.aspx[quote][b]DB_Newbie2007 (1/2/2013)[/b][hr]Full backups are done weekly and log backups daily. [/quote]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.Wed, 02 Jan 2013 15:45:59 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401824-357-1.aspxGail, 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! :)Wed, 02 Jan 2013 14:04:24 GMTDB_Newbie2007http://www.sqlservercentral.com/Forums/Topic1401824-357-1.aspxCheckDB 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 logsOr, if you don't have backups (or can't afford to lose the data since the last log backup)Switch to simple recovery modelTruncate the log (checkpoint)Shrink the logGrow it again (perhaps after moving to a new storage array)Switch back to full recoveryTake a full backupRestart log backupsThe second 'solution' may not work. The first is recommended.Wed, 02 Jan 2013 11:44:59 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401824-357-1.aspxI'm wondering, in looking at your two statements, the error message:[quote]A nonrecoverable I/O error occurred on file "E:\SQLLogs\MyDB\MyDB.ldf:" 1(Incorrect function.).[/quote]Then your backup code:[quote]BACKUP LOG [MyDB] TO DISK = 'M:\Backups\DataCap\MyDB_log_20130102.bak' WITH RETAINDAYS = 7, INIT[/quote]If [b]possibly[/b] 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.JasonWed, 02 Jan 2013 11:16:17 GMTjasona.workhttp://www.sqlservercentral.com/Forums/Topic1401824-357-1.aspx[quote][b]DB_Newbie2007 (1/2/2013)[/b][hr]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, [u][url=http://social.technet.microsoft.com/Forums/en-US/windowsbackup/thread/5e882fba-1e97-43c1-bfc1-9d769165da6f]http://social.technet.microsoft.com/Forums/en-US/windowsbackup/thread/5e882fba-1e97-43c1-bfc1-9d769165da6f[/url][/u] 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.[/quote]Anything interesting in the Windows Event logs?Wed, 02 Jan 2013 10:49:49 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1401824-357-1.aspxLOL. 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.Wed, 02 Jan 2013 10:26:06 GMTDB_Newbie2007http://www.sqlservercentral.com/Forums/Topic1401824-357-1.aspxDid you mean DBCC CHECKDB or the operating system routine CHKDSK?Wed, 02 Jan 2013 10:07:37 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1401824-357-1.aspxWe started receiving this message mid-last week:[b]Msg 3271, Level 16, State 1, Line 4A nonrecoverable I/O error occurred on file "E:\SQLLogs\MyDB\MyDB.ldf:" 1(Incorrect function.).Msg 3013, Level 16, State 1, Line 4BACKUP LOG is terminating abnormally.[/b]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, INITI 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 3This 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?Wed, 02 Jan 2013 07:07:37 GMTDB_Newbie2007