create procedure sys.sp_check_log_shipping_monitor_alert
as
begin
set nocount on
declare @retcode int
,@server sysname
,@database sysname
,@threshold_alert int
,@curdate_utc datetime
,@threshold int
,@elapsedtime int
,@latency int
-- security check
exec @retcode = sys.sp_MSlogshippingsysadmincheck
if (@retcode != 0 or @@error != 0)
return 1
-- must be invoked from master db
if (db_name() != N'master')
begin
raiserror(5001, 16,-1)
return 1
end
select @curdate_utc = getutcdate()
-- enumerate through the primary and secondaries
-- which have crossed the thresholds
declare #hclsalert cursor local fast_forward for
(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))
open #hclsalert
fetch #hclsalert into @server, @database, @threshold_alert, @threshold, @elapsedtime, @latency
while (@@fetch_status != -1)
begin
-- log error that raises alerts
raiserror(@threshold_alert, 16, 1, @server, @database, @threshold, @elapsedtime, @latency)
fetch #hclsalert into @server, @database, @threshold_alert, @threshold, @elapsedtime, @latency
end
close #hclsalert
deallocate #hclsalert
-- all done
return 0
end