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.
Thanks Eric, I've had the same issue for a week and this just solved it for me.
ReplyDeleteCheers Dan
When you script out the job, you need to read what it says rather than just running it.
ReplyDeleteIt 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.
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