Home / server / Questions / 631266
In Process
kishore
Asked: 2014-09-26 06:45:29 +0800 CST

Sql Server Transaction Log Backup Fails

  • 772

I have a SQL Server 2008 Standard version. Mirroring is set up on the server in full safety mode. It has 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

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

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

sql-server

1 Answers

  1. Thank you every one for responding and helping me on this. It turns out 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

    • 1