2

I am receiving the Log shipping Primary Server Alert warning in error

In msdb.dbo.log_shipping_monitor_primary the last backup time is 1215 however on that machine there are .trn files that have been created since

In msdb.dbo.log_shipping_monitor_secondary the destination is updating as I'd expect it to

So the .trn files are being created correctly, transferred correctly, and saved correctly, however SSMS appears to be unaware of this hence the warnings that are being triggered in error

Any ideas on why this might be happening? I set up TLS around 3 months ago and apart from a few little problems it's worked fine, this has only occurred today

edit: Rather than the full output as there are a lot of databases being backed up, I've added below the relevant mismatch, please excuse my formatting I'm not sure exactly how to neaten this up

msdb.dbo.log_shipping_monitor_primary

primary_server  primary_database    backup_threshold threshold_alert    threshold_alert_enabled last_backup_file                        last_backup_date        last_backup_date_utc    history_retention_period
DREAM           ACCDRE              60               14420              1                       e:\tlshipping\ACCDRE_20170209121501.trn 2017-02-09 12:15:02.010 2017-02-09 12:15:02.003 5760

msdb.dbo.log_shipping_monitor_secondary

secondary_server secondary_database primary_server  primary_database    restore_threshold   threshold_alert threshold_alert_enabled last_copied_file                                last_copied_date        last_copied_date_utc    last_restored_file                              last_restored_date      last_restored_date_utc  last_restored_latency   history_retention_period
WHSQLDR          ACCDRE             DREAM           ACCDRE              45                  14420           1                       E:\DreamLogShipping\ACCDRE_20170209163001.trn   2017-02-09 16:32:02.397 2017-02-09 16:32:02.397 E:\DreamLogShipping\ACCDRE_20170209163001.trn   2017-02-09 16:34:11.477 2017-02-09 16:34:11.477 4                       5760
  • Can you add output of select * from msdb.dbo.log_shipping_monitor_primary. and select * from msdb.dbo.log_shipping_monitor_secondary – Shanky Feb 9 '17 at 16:59
  • I've added the data relevant to the one tls job I'm having a problem with, hope that's ok – cockbeard Feb 9 '17 at 17:25
  • where have you configure your monitor server? – jesijesi Feb 9 '17 at 20:02
  • Run EXEC sys.sp_help_log_shipping_monitor on both the Primary and Secondary servers. Does this reveal anything? – MillhouseD Feb 9 '17 at 20:18
  • Any chance the .trns are very small due to no activity in the primary DB? – Kevin3NF Feb 9 '17 at 21:05
2

You are correct the last backup time is showing 2017-02-09 12:15:02.010 while last copied and restored are much greater 2017-02-09 16:32:02.397 and 2017-02-09 16:34:11.477. So I believe somehow the catalog is not getting updated. There can be two reasons:

  1. This can be a bug make sure SQL Server 2008 R2 is patched to SP3

  2. There was old logshipping which was configured and was not removed correctly and completely.

I believe the solution here would be to drop and recreate log shipping.

| improve this answer | |
  • I was initially loathe to simply "turn it off and on again", but given the root cause was likely a network issue which I was unaware of until much later in the day. This led me to decide that it was very unlikely to randomly recur, and since recreating the shipping it now syncs as expected – cockbeard Feb 10 '17 at 9:22
  • So according to you this was network issue ? Well that is hard to believe – Shanky Feb 10 '17 at 9:51
  • 1
    I only found out about the network issue later, but it happened around 1220 yesterday. Network issue occurred when domain controller for some reason picked up the wrong date and was feeding incorrect datetime to client machines, this made most communication fail due to mismatching time/date. I can't say that this definitely the cause, but it would be highly coincidental if it wasn't, especially given the timing of the failure and that the one job that failed was the only one that was running from a separate physical machine – cockbeard Feb 10 '17 at 10:00
  • So yeah very very strange and thanks for your assistance, as much as I'd wanted to recreate the issue to find the cause I think I'll just have to put this one down to experience and/or gremlins – cockbeard Feb 10 '17 at 10:02
  • Hmm...seems likely. Thanks for heads up though – Shanky Feb 10 '17 at 10:02
0

This might be a permission issue. I am assuming that the monitor server is not on the primary and the issue could be that the primary server is not able to update the values in the monitor server.

Can you check where your monitor server is and make sure primary server service account has permission to update the monitor server msdb.

| improve this answer | |

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.