Finding SQL Server’s job execution history using TSQL (High Level)

This is a related post from http://blog.consultdba.com/2012/06/finding-sql-servers-job-execution.html. If you need to find execution history of all SQL Server’s job but do not need to know how much time each step of a job took, then please use below code (TSQL Script):

SELECT      sj.name,

sja.run_requested_date,

CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration

FROM  msdb.dbo.sysjobactivity sja

INNER JOIN msdb.dbo.sysjobs sj

ON    sja.job_id = sj.job_id

WHERE sja.run_requested_date IS NOT NULL

ORDER BY sja.run_requested_date desc

Note: This code is tested on SQL 2005/2008. It will not run on SQL 2000.

Reference: http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx

Note: You may modify where clause to limit this report to one or some specific job/s.