I apologize in advance for a long post, but I have had it up to here with the situation of having to delete Log Shipping (LS) configuration and starting it over for any DB that has got this error.

I have LS setup on 3 win2k8r2 servers (primary, secondary, monitor) with 100 databases backed up and shipped transactions from the primary to secondary and monitored by monitor. Backups and copies are run every 15 mins and then the ones older than 24 hrs are deleted.

Some DBs are very active and some not so active but shipped regardless for uniformity sake (basically to make secondary server identical to primary). Some DBs are for SharePoint 2010 and the majority are for inhouse apps.

The issue is that after all LS configs are set up, everything works well for about 3 to 4 days. Then I go to the Transaction LS Status report on the secondary, I see that randomly some LS jobs have an Alert Status, because the time since last copy is over 45 mins so no restore has occurred. This seems random and the only errors I see is from a SharePoint 2010 DB (WebAnalyticsServiceApplication_ReportingDB_77a60938_##########) which I belive is a Reporting DB that gets created weekly and LS just cannot figure out which the last copy to backup or to restore is.

I posted here regarding that and I have yet to find a permanent solution.

For my main error (time since last copy) I have not seen anything that could have caused that and I don't get any messages (even though some alert statuses have been ignored for 3 days).

Anyway, I would really appreciate any input on understanding what's causing this issue and how I could fix it.

  • Are the target databases in use at all (standby)? – Jon Seigel Jul 9 '13 at 19:58
  • I belive is a reports db that gets created weekly and LS cannot just figure which the last copy to backup or to restore is when a database is recreated, you have to re-setup logshipping from scratch. What is the error you are getting ? – Kin Shah Jul 9 '13 at 21:21
  • @Kin the error regarding my previous post with the SP reports db is this Cannot open backup device '\\SQLSecondary\LS-BackUP\WebAnalyticsServiceApplication_ReportingDB_77a60938-##########.trn'. Operating system error 2(The system cannot find the file specified.). RESTORE HEADERONLY is terminating abnormally. But for this post, Could not find a log backup file that could be applied to secondary database 'InHouseDB'. when I run 'SELECT * FROM [msdb].[dbo].[log_shipping_monitor_history_detail]' on the monitor server – shilezi Jul 10 '13 at 13:47
  • @JonSeigel Nope, they're all for warm DR and are Standby/Read-Only – shilezi Jul 10 '13 at 13:51
  • 1
    @Kin On the Primary server using the LS GUI, I chose to "Delete files older than: 24hrs" and the Backup job schedule to occur every day every 15 minute(s) between 12:00:00 AM and 11:59:00 PM. And on the Secondary server, files are copied using the same/default schedule and i chose the "Delete copied files after:24hrs". Once initialized, i watch the backup, copy and restore happen hassle free and 3 days later, 4 or 5 of 100 dbs won't copy. – shilezi Jul 10 '13 at 14:13

Restarting both the source and destination servers housing the sql engines seemed to fix the issue. its been a week and all backup, copy and restore has no issues except for just that one SharePoint reporting db WebAnalyticsServiceApplication_ReportingDB_77a60938-####-####-####-##########. Thanks to everyone for their input, as I at least came out of this more knowledgeable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.