Question: How can I programmatically return SQL Agent jobs which failed in the last 24 hrs. I was running the following code and it didn’t return the results as expected ?
This job is part of some automated DBA tasks
SELECT * FROM msdb..sysjobhistory WHERE run_status NOT IN (1,4) DATEDIFF(hh, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), GETDATE()) <= 24
Answer: The code above will return failed jobs via the WHERE run_status NOT IN (1,4)
, but not for the timeframe you expect. The reason: The code doesn’t account for sjh.run_date being an INT. The CONVERT is not having the desired result.
Use the msdb.dbo.agent_datetime function. The msdb.dbo.agent_datetime function converts run_date & run_time into datetime values. This function allows for much cleaner code , and makes you wonder why the datatype was never a datetime originally.
This will return the desired result:
SELECT * FROM msdb..sysjobhistory WHERE run_status NOT IN (1,4) AND DATEDIFF(hh, msdb.dbo.agent_datetime(run_date,run_time), GETDATE()) <= 24