This is a follow-up post on SQL Server – Check SQL Agent Job History using T-SQL.
SQL Server Agent stores SQL jobs history in sysjobhistory. It has two different columns for date and time, Run_Date and Run_Time. Since this is not available as DATETIME we cannot filter based on certain criteria such as jobs that run in last 24 hours.
There is a system function available in msdb database which takes these two columns as input converts output to a DATETIME data type, dbo.agent_datetime. You can use this function as below:
SELECT TOP 5
[JobName] = JOB.name,
[StepName] = HIST.step_name,
[RunDateTime] = dbo.agent_datetime(HIST.run_date,HIST.run_time)
FROM sysjobs JOB
INNER JOIN sysjobhistory HIST ON HIST.job_id = JOB.job_id
ORDERBY HIST.run_date, HIST.run_time
JobName StepName RunDateTime
syspolicy_purge_history Verify that automation is enabled. 2013-01-29 02:00:00.000
syspolicy_purge_history Purge history. 2013-01-29 02:00:00.000
syspolicy_purge_history Erase Phantom System Health Records 2013-01-29 02:00:00.000
syspolicy_purge_history (Job outcome) 2013-01-29 02:00:00.000
syspolicy_purge_history Verify that automation is enabled. 2013-01-30 02:00:00.000
(5 row(s) affected)
Hope This Helps!
Filed under: Catalog Views, SQL Agent, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Undocumented Functions