Alert when Job isn't triggered

Question

I have a Sql Job on my server that is triggered nightly from a remote server (once it has completed its backups).

I have successfully configured the job to notify me when it fails, but I would like to set up an alert to notify me when a job has not been triggered. i.e. when the remote server fails to trigger the job on my end.

Is this possible? I've had a look at the settings, and all the alerts, etc, are all concerned with when the job fails/succeeds.

I could set the job to alert on completion (regardless of status), and I was doing that to start with (and I did that to start with), but I would rather not get continuous emails (after all, I'd probably just start ignoring them).

score 8 · Accepted Answer · answered Aug 13 '14 at 09:50

I have managed to find one solution to this, although it's not as neat as I would have preferred. I've created a second job that checks the last run date on my target job. This is scheduled to run daily, a couple of hours after the target job should have run.

If not exists 
(
    SELECT 'x'
    FROM msdb.dbo.SysJobServers S
        INNER JOIN msdb.dbo.SysJobs J ON S.job_id = J.job_id
    WHERE J.name = 'JobName'
        AND CONVERT(datetime2, CONVERT(VARCHAR(20), last_run_date)) > GETDATE() -1
)
BEGIN
    RAISERROR ('The Job has not been executed today.', 16, 0)
END

score 0 · Answer 2 · answered Aug 13 '14 at 09:50

For this I would create a second monitoring job to look at sysjobhistory in msdb. This will work best if your current job typically runs within a fairly well defined time interval. If it doesn't then you cannot schedule the monitoring job until quite late in the day, by when it may be too late to do anything meaningful to correct the situation. Be aware that sysjobhistory holds a limited number of rows. If you have other jobs which run very frequently they may push this job's history out of the table before the monitoring job can see it. If this is a problem create a custom logging table in an application database and get your current job to write to it.

I haven't yet figured out how to get informed if the monitoring job doesn't run :-)

Alert when Job isn't triggered

2 Answers2