SQLTeam.com | Weblogs | Forums

Log Shipping Backup Job Stuck at In Progress for several days

sql2012

#1

I have a SQL 2012 server with Log Shipping for 6 databases to a secondary server on the same network, however when checking SQL Agent for the backup job this is still at In Progress for all 6 databases, these have been stuck at In Progress for 4 days now, I have even stopped and deleted the configuration and re-initialized the log shipping from scratch on all databases but this just seems stay stuck at In Progress, the log files for these databases are in the region of 3GB each. I have other servers log shipping without any issues.

The jobs are set to log ship every 15 minutes but I have tried this at every 1 hour, the files are kept for 24 hours and the alert is in 60 minutes if no backup has taken place. There are no errors on either the Primary or Secondary, the Copy and Restore jobs are executing ok but obviously logging a message that there are no files.

I have even restarted SQL Server.

Can anyone offer any advice as to what this might be or how I can fix this, short of rebooting the server, which I'm not sure would help, I am stuck as to what else to try.


#2

If I were you, I will try to fix the issue first then look at "why" it happen afterwards, in case anything bad happen now.

You can simply stop those backup job, check the log shipping status, if they are all out-sync, make a manual backup of the DB, restore them over to DR server (with norecovery) and the backup itself should sync. Then monitor the backup job for log shipping for the hour, ensure it can perform the backup and restore on the other side, then the issue should be gone.

Then you can check the log and whatever things you can find to see why it actually happen.

hope this helps