I have this problems, that if a schedule inside a Agent job, have not been started for some reason, there will be no errors about this problem.
So Í have created this script, that will give the Status of 1, if the job, haven’t been run for the last two hours.
I used PRTG to check the status.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
USE msdb GO DECLARE @JobName VARCHAR(1000) DECLARE @LastRunTime VARCHAR(1000) DECLARE @LastRunStatus VARCHAR(1000) DECLARE @CurrentTime VARCHAR(20) SET @JobName = 'JOBNAME' SET @CurrentTime = LEFT(REPLACE(CONVERT (time, DATEADD(HOUR, - 2, GETDATE())),':',''),6) SELECT DISTINCT @LastRunTime = SJH.run_time FROM sysjobhistory SJH, sysjobs SJ WHERE SJ.[name] = @JobName AND SJH.job_id = SJ.job_id and SJH.run_date = (SELECT MAX(SJH1.run_date) FROM sysjobhistory SJH1 WHERE SJH.job_id = SJH1.job_id) IF @LastRunTime >= @CurrentTime SELECT '0' AS Status ELSE SELECT '1' AS Status |
In line 10, replace – 2 with the number of hours, you want to go back
Below is the same script, just for daily jobs, so it will will check if the job haven’t been run in the last 25 hours (My Schedule is set to run every 24 hours, and i give it an hour to be finish, before i got an alarm.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DECLARE @JobName VARCHAR(1000) DECLARE @LastRunDate VARCHAR(1000) DECLARE @LastRunStatus VARCHAR(1000) DECLARE @CurrentDate VARCHAR(20) -- used for file name SET @JobName = 'JOBNAME' SET @CurrentDate = REPLACE(CONVERT (date, DATEADD(HOUR, -25, GETDATE())),'-','') SELECT DISTINCT @LastRunDate = SJH.run_date FROM sysjobhistory SJH, sysjobs SJ WHERE SJ.[name] = 'Fibia optimering daglig (Batch 2)' AND SJH.job_id = SJ.job_id and SJH.run_date = (SELECT MAX(SJH1.run_date) FROM sysjobhistory SJH1 WHERE SJH.job_id = SJH1.job_id) ORDER BY SJH.run_date desc IF @LastRunDate >= @CurrentDate SELECT '0' as Status ELSE SELECT '1' as Status |
In line 10, replace – 25 with the number of hours, you want to go back