I am trying to solve some issues regarding SQL Server jobs history from last several days with no luck. I like to show job history data as see in Log File Viewer. I run queries and get data but i am confused on how to know which step belongs to job running at particular time. Say if we have a job running every hour and it has 4 steps so when every the job runs it will insert 5 records in sysjobhistory for a successful run now i am concerned about which steps in belongs to which job running at particular time. How i can show it in a drill down report if i want to create one.
SELECT sysjobhistory.server,
sysjobs.name
AS
job_name,
CASE sysjobhistory.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
ELSE '???'
END
AS
run_status,
Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' +
Substring(CONVERT(VARCHAR
(8), run_date), 5, 2) + '-' +
Substring(CONVERT(VARCHAR(
8), run_date), 7, 2), '')
AS
[Run DATE],
Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':'
+
Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2
)
+
':' +
Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '')
AS
[Run TIME],
Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) +
':' +
Substring(CONVERT(VARCHAR(7), run_duration+1000000),
4,
2)
+ ':' +
Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2),
''
) AS
[Duration],
sysjobhistory.step_id,
sysjobhistory.step_name,
sysjobhistory.MESSAGE
FROM msdb.dbo.sysjobhistory
INNER JOIN msdb.dbo.sysjobs
ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
ORDER BY instance_id DESC
You could try this query. It creates a temp table of jobs based on the step_id = 0 assigning each record a unique identifier. Then it joins back to the job history table using the run time and duration. So, all the steps of one job will have the same RUN_INSTANCE value.
-- create a temporary table of instances when a job was initiated
declare @JOBS table
(
RUN_INSTANCE uniqueidentifier,
job_id uniqueidentifier,
name sysname,
run_status int,
run_date int,
run_time int,
run_duration int
);
-- insert one record for each instanced job and assign it a unique identifier
insert into @JOBS
select
RUN_INSTANCE = NewID(),
h.job_id,
j.name,
h.run_status,
h.run_date,
h.run_time,
h.run_duration
from msdb.dbo.sysjobhistory h
join msdb.dbo.sysjobs j on j.job_id = h.job_id
where step_id = 0
-- query the jobs history
select
h.server,
j.RUN_INSTANCE,
j.name,
h.step_id,
h.run_date,
h.run_time,
run_status =
case h.run_status
when 0 then 'failed'
when 1 then 'succeeded'
when 2 then 'retry'
when 3 then 'canceled'
when 4 then 'in progress'
else '???'
end,
h.message
from @JOBS j
join msdb.dbo.sysjobhistory h on
h.job_id = j.job_id
and convert(varchar(20),h.run_date) + convert(varchar(20),h.run_time)
between convert(varchar(20),j.run_date) + convert(varchar(20),j.run_time)
and convert(varchar(20),j.run_date) + convert(varchar(20),j.run_time + j.run_duration)
order by j.RUN_INSTANCE, h.step_id