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