create procedure sys.sp_delete_log_shipping_alert_job_internal
as
begin
set nocount on
declare @retcode int
,@alert_job_id uniqueidentifier
-- remove if alert job exists
select @alert_job_id = alert_job_id from msdb.dbo.log_shipping_monitor_alert
if (@alert_job_id is not null)
begin
if not exists (select * from msdb.dbo.log_shipping_monitor_primary)
and not exists (select * from msdb.dbo.log_shipping_monitor_secondary)
begin
-- drop alert job for the server
exec @retcode = sys.sp_MSprocesslogshippingjob @type = 4
,@mode = 2
,@jobid = @alert_job_id
if (@retcode != 0 or @@error != 0)
return 1
-- delete entry for alert job
delete msdb.dbo.log_shipping_monitor_alert
where alert_job_id = @alert_job_id
if (@@error != 0)
return 1
end
end
-- all done
return 0
end