November 21, 2013 at 8:51 am
2013-11-21 13:00:31.95Skipping log backup file '\\as2\dbbackups\TransactionLog\ReportOtherCarrierLive\OtherCarrierLive_20131121124501.trn' for secondary database 'ReportOtherCarrierLive' because the file could not be verified.
This line in the error is where I would start. Do you see the same filename (OtherCarrierLive_20131121124501.trn) in every failure of the restore. if so check if the file exists in the specified location. This location should be the destination of the copy job. if the file does exist there check if it exists in your backup location for the primary. If you can find both files compare them(size, modified,etc).
November 21, 2013 at 8:53 am
The files exist and are being copied correctly every 15 minutes
November 21, 2013 at 9:26 am
possibly the file \\as2\dbbackups\TransactionLog\ReportOtherCarrierLive\OtherCarrierLive_20131121124501.trn is in use and cannot be accessed by SQL server. You can quickly test this by trying to move(not copy) it to a different folder or use Process Explorer or handle to identify which process(if any) is accessing the file.
You had mention that only two out of several secondaries are failing. Do the one that are working successfully restore thier backups from the same location (\\as2\dbbackups\TransactionLog\ReportOtherCarrierLive\)? I would guess if it is the same it would be here \\as2\dbbackups\TransactionLog\ with ReportOtherCarrierLive being specific to the database. Does the sql agent service account still have permission to this share.
November 22, 2013 at 4:12 am
Each database saves to the same location on a NAS drive, but into a database specific subdirectory, and the SQL service has full permissions to the location
November 22, 2013 at 10:53 am
you could try restoring the file manually. If it works then that will prove there are no issues with the file. If it fails you may get a different error.
restore log ReportOtherCarrierLive from '\as2\dbbackups\TransactionLog\ReportOtherCarrierLive\OtherCarrierLive_20131121124501.trn' with norecovery;
November 25, 2013 at 3:00 am
A restore of the offending transaction log worked.
I have noticed that the database, when the log shipping fails, is shown as "Restoring" in the database list, so has the previous log restore "stalled" in some way?
November 25, 2013 at 11:15 am
log shipping secondaries will either be in restoring (norecovery) or standby. Do you set your secondaries to standby (thus restoring is an abnormal state)?
so you can restore the file manually. Does the next trans log backup fail in the same way?
check msdb.dbo.log_shipping_monitor_error_detail on the secondary server or the monitor server(should be the same info as your error).
SELECT log_time,message
FROM log_shipping_monitor_error_detail
where database_name = '<dbname>'
November 26, 2013 at 2:57 am
The log shipping is set to leave the target in standby mode, but when it goes wrong it seems to be leaving the database in no recovery mode
November 26, 2013 at 9:04 am
does your undo(.tuf) file exist and is the creation time consistent with the failed load or the last successful load?
Are there any errors in the sql error log or the application event log at the time of the failed tlog restore?
January 23, 2015 at 1:50 am
Dear Chris,
Please advise if this issue was resolved. I am also facing the similar issue.
Viewing 10 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply