SQL Agent Job and Job Step History in SQL Server

To generate a comprehensive and descriptive set from the job history the following script can be run in SSMS/QA. It uses the system database msdb and some of the system tables related to jobs and job history.

USE msdb
Go
SELECT j.name JobName,h.step_name StepName, h.step_id,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
Where j.name = 'XXXXXXXXXXX'
ORDER BY h.run_date, h.run_time
Desc
GO

Column	Description
[JobName]	Name of job as specified
[StepName]	Name of step as specified
[RunDate]	Date when job run
[RunTime]	Time when job run
[StepDuration]	Duration in seconds that a step took to complete
[ExecutionStatus]	Execution status of step
[MessageGenerated]	Message generated at end of step