4

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).

7

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
| improve this answer | |
  • I am receiving this error when i execute the above: Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string. – Raidenlee Jun 8 '18 at 16:10
  • @Raidenlee That'll only happen when the job has never been run. So long as it's been run at least once, the script above will work fine. If you need it to handle never being run, you can replace last_run_date with Case When last_run_date = 0 Then 19000101 Else last_run_date END – Obsidian Phoenix Jun 11 '18 at 9:57
0

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 :-)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.