Sql Server Transaction Log Backup Fails

1
votes

I have a Sql Server 2008 Standard version. Mirroring is set up on the server in full safety mode. Its been working fine till today. The transaction log back-up fails every-time with an error

"Error: 2014-09-25 08:34:33.17 Code: 0xC002F210 Source: JuneDB Log Backup Execute SQL Task Description: Executing the query "BACKUP LOG [JuneDB] TO DISK = N'H:\BKs\Hou..." failed with the following error: "Read on "E:\LDFs\JuneDB.ldf" failed: 1(Incorrect function.) BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"

  • I am using a Maintenance plan for taking backups.
  • The drive also contains log files of 5 other databases and their log backups are fine.
  • This problem started after successfully completing rebuild indexes maintenance plan.
  • Full backups do not have any problem.

I am not able to identify why reading the log file of this one database is erroring out. How am I supposed to proceed on this issue.

Things I tried

  1. Ran DBCC CHECKDB([JuneDB]) WITH NO_INFOMSGS returned no error messages
  2. Ran a query to take transaction backup instead of using a Maintenance plan. It gave same error Edit

Update I just noticed at 4:30 AM we ran a maintenance plan to rebuild all indexes. Looking at the error log, I started getting errors for Transaction log backups after 4:30 Am. I am not sure how rebuild indexes could possibly cause the transaction log backups to fail but they sure seem related

1
sqlsql-serversql-server-2008sql-server-2005backup
Have you tried manually running the transaction log backup? - SQLChao
I tried and it manually and it gave same error "Msg 3203, Level 16, State 1, Line 1 Read on "E:\LDFs\JuneDB.ldf" failed: 1(Incorrect function.) Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating abnormally. - kishore
Have you verified the file is there in `E:\ldfs? Test by pathing from the SQL server to the directory. The error sounds like there is a problem reading the file because it isn't there or maybe a permissions issue. I haven't run into this exact error before but I have seen a 3rd party vendor's software update change recovery models screwing up my backups. - SQLChao
Yes the file exists and our backup plan was working fine since 4 years, It is only today we started receiving errors. - kishore

1 Answers

0
votes

Thank you every one for responding and helping me on this. It turns out to be a log file corruption. Below steps solved my issue

  • Stop mirroring
  • Switching the database to the Simple recovery model
  • Performing a checkpoint (which should clear the active log as long as nothing else requires the log to be kept active)
  • Switching back to the Full recovery model
  • Reestablishing the log backup chain by performing a full backup
  • Start mirroring

http://sqlmag.com/blog/transaction-log-corruption-and-backups