An alert for Log backup which is less than 2 hours
The below script will create a SQL Server Agent Job that runs hourly.  The job reports, via database mail, whenever it detects a database that hasn't had a log backup in the past 2 hours.  Since log backups don't apply to databases in the simple recovery model, those databases are excluded.  Also, the model database is excluded - you may want to remove that exclusion if you make frequent modifications to the model database (why would you, though).

The job is schedule to run starting at 6:00 am each day, and stop running at 10:00 pm.  You'll need to modify the @recipients and @profile_name variables in the first couple of lines of the script.

The job is set to notify the SQL Server Agent Operator named 'DBA', so you'll need to set that up in advance.  You can use dbo.sp_add_operator to add an operator via T-SQL.

USE [msdb]
GO
DECLARE @recipients varchar(max) = '&lt;your_email_here&gt;';
DECLARE @profile_name sysname = N'&lt;database_mail_profile_here&gt;';

DECLARE @cmd nvarchar(max) = N'DECLARE @msg nvarchar(max);
SET @msg = N'''';
;WITH src AS 
(
SELECT bs.database_name
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.type = ''L'' --only log backups
    AND bs.backup_finish_date &gt; DATEADD(MINUTE, -120, GETDATE())
)
, last_backup AS (
SELECT bs.database_name
    , bs.backup_start_date
    , rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC)
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.type = ''L'' --only log backups
    AND bs.backup_finish_date &lt;= DATEADD(MINUTE, -120, GETDATE())
)
SELECT @msg = @msg + CASE WHEN @msg = N'''' THEN N'''' ELSE N''&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td&gt;'' END 
    + d.name + CASE WHEN lb.backup_start_date IS NOT NULL AND lb.backup_start_date &lt;&gt; N'''' THEN N'' (last log backup: '' + CONVERT(nvarchar(30), lb.backup_start_date, 120) + N'')'' ELSE N'''' END 
FROM sys.databases d
    LEFT JOIN last_backup lb ON d.name = lb.database_name AND lb.rn = 1
WHERE d.state_desc = N''ONLINE''
    AND d.recovery_model_desc &lt;&gt; N''SIMPLE'' --simple recovery affords no log backups
    AND d.name &lt;&gt; CONVERT(sysname, N''model'')
    AND NOT EXISTS (
        SELECT 1
        FROM src
        WHERE src.database_name = d.name
        )
ORDER BY d.name;

IF @msg &lt;&gt; N''''
BEGIN
    SET @msg = N''&lt;html&gt;
    &lt;style&gt;body {
        font-family:Segoe UI,Verdana,Tahoma;
        font-size:10pt;
    }
    &lt;/style&gt;
    &lt;body&gt;
    &lt;p&gt;The following databases haven''''t had a log backup during the last 2 hour period:&lt;/p&gt;&lt;br /&gt;
    &lt;table&gt;&lt;td&gt;&lt;tr&gt;'' + @msg + N''&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;
    &lt;/body&gt;
    &lt;/html&gt;'';
    DECLARE @subject nvarchar(1000);
    SET @subject = ''Log Backup Notification from SQL Server: '' + @@SERVERNAME;

    EXEC msdb.dbo.sp_send_dbmail
          @profile_name = ''' + @profile_name + N'''
        , @recipients = ''' + @recipients + N'''
        , @subject = @subject
        , @body = @msg
        , @body_format = ''HTML'';
END
';

DECLARE @ReturnCode int;
SELECT @ReturnCode = 0;

BEGIN TRANSACTION;

BEGIN TRY
    IF NOT EXISTS (
        SELECT sc.name 
        FROM msdb.dbo.syscategories sc
        WHERE sc.name = N'Log Notifications' AND category_class=1
        )
    BEGIN
        EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
            , @type = N'LOCAL'
            , @name = N'Log Notifications'
        IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) THROW 50000, N'An error occurred adding the Job Category', 1;
    END

    DECLARE @jobId binary(16);

    IF EXISTS (
        SELECT 1
        FROM msdb.dbo.sysjobs sj
        WHERE sj.name = CONVERT(sysname, N'Log Backup Notifier')
        )
    BEGIN
        EXEC msdb.dbo.sp_delete_job @job_name = N'Log Backup Notifier'
            , @delete_history = 0
            , @delete_unused_schedule = 0;
    END

    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Log Backup Notifier' 
        , @enabled = 1
        , @notify_level_eventlog = 3
        , @notify_level_email = 2
        , @notify_level_netsend = 0
        , @notify_level_page = 0
        , @delete_level = 0
        , @description = N'No description available.'
        , @category_name = N'Log Notifications'
        , @owner_login_name = N'sa'
        , @notify_email_operator_name = N'DBA'
        , @job_id = @jobId OUTPUT;

    IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) THROW 50000, N'An error occurred adding the Job', 1;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
        , @step_name=N'Email DBAs if no log backup has occurred in 2 hours'
        , @step_id = 1
        , @cmdexec_success_code = 0
        , @on_success_action = 1
        , @on_success_step_id = 0
        , @on_fail_action = 2
        , @on_fail_step_id = 0
        , @retry_attempts = 0
        , @retry_interval = 0
        , @os_run_priority=0, @subsystem = N'TSQL'
        , @command = @cmd
        , @database_name = N'msdb'
        , @flags = 0;

    IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) THROW 50000, N'An error occurrred adding the Job Step', 1;

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
        , @start_step_id = 1;

    IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) THROW 50000, N'An error occurred setting the job starting step.', 1;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
        , @server_name = N'(local)';

    IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) THROW 50000, N'An error occurred setting the job server to (local)', 1;

    IF EXISTS (
        SELECT 1
        FROM msdb.dbo.sysschedules ss
        WHERE ss.name = N'Log Notification Schedule'
        )
    BEGIN
        EXEC msdb.dbo.sp_delete_schedule @schedule_name = N'Log Notification Schedule';
    END
    DECLARE @start_date int;
    SET @start_date = CONVERT(int, CONVERT(varchar(8), GETDATE(), 112));
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId
        , @name = N'Log Notification Schedule'
        , @enabled = 1
        , @freq_type = 4
        , @freq_interval = 1
        , @freq_subday_type = 8
        , @freq_subday_interval = 1
        , @freq_relative_interval = 0
        , @freq_recurrence_factor = 0
        , @active_start_date = @start_date
        , @active_end_date = 99991231
        , @active_start_time = 60000
        , @active_end_time = 215959;

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF (@@TRANCOUNT &gt; 0) 
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 
    END
END CATCH
GO