patternsqlMinor
Alert when Job isn't triggered
Viewed 0 times
alertwhenisnjobtriggered
Problem
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).
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).
Solution
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)
ENDCode Snippets
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)
ENDContext
StackExchange Database Administrators Q#73873, answer score: 8
Revisions (0)
No revisions yet.