Thursday, July 8, 2010

Log Shipping error

I had a problem where I noticed on my primary log shipping machine (the one the data originates on) that the LS_Alerts job was failing. It said that a new database we were working on was failing. I also noticed that I had two jobs that didn't belong on that machine..I had a LS_Copy and a LS_Restore...which should have been on the secondary machine. I believe that another admin set it up from the development machine during the initial phase for some reason, but I couldn't find a source machine. I delete the two jobs from the Job manager, but the Alert job still showed failures when run. I decided to figure out what was the problem, and this is what I did to look into it:

I saw that the alert job ran the proc sys.sp_check_log_shipping_monitor_alert, so I opened up the proc to see what it was doing. It calls the following:


(select primary_server
,primary_database
,isnull(threshold_alert, 14420)
,backup_threshold
,datediff(minute, last_backup_date_utc, @curdate_utc)
,cast(0 as int)
from msdb.dbo.log_shipping_monitor_primary
where threshold_alert_enabled = 1
and datediff(minute, last_backup_date_utc, @curdate_utc) > backup_threshold)
union
(select secondary_server
,secondary_database
,isnull(threshold_alert, 14421)
,restore_threshold
,datediff(minute, last_restored_date_utc, @curdate_utc)
,isnull(last_restored_latency,0)
from msdb.dbo.log_shipping_monitor_secondary
where threshold_alert_enabled = 1
and (datediff(minute, last_restored_date_utc, @curdate_utc) > restore_threshold
or last_restored_latency > restore_threshold))



and raises an error if there are records.

When I ran it on my primary machine the second statement (from log_shipping_monitor_secondary) had a record in it; but the odd thing was that when I just did a select * against the table, I saw that the primary server for that statement was the same server as the secondary server...so it was set to logship to its self. From that point, it was simple, delete the record and the error went away.

Hope that helps someone in the future.

3 comments:

  1. Thanks Eric, I've had the same issue for a week and this just solved it for me.

    Cheers Dan

    ReplyDelete
  2. When you script out the job, you need to read what it says rather than just running it.

    It has a comment at the top saying

    "Execute the following statements at the Primary to configure Log Shipping"

    then further down the script there is another comment

    "Execute the following statements at the Secondary to configure Log Shipping"

    So that's likely why you got the extra jobs on the primary that you did not need.

    ReplyDelete
    Replies
    1. The purpose of the post wasn't about the cause of the error...I agree with you on the cause, it was about how to fix it, if you encounter the issue.

      Delete