September 16, 2012 at 9:48 pm
Hopefully this is the right forum. It's fundamentally a T-SQL question.
I'm trying to make a report that is useful for our network sysadmins to monitor the SQL Agent jobs from 23 different servers. They aren't keen on using SSMS to connect to many servers and manually check each job so I'm doing an exception report to try and make their lives easier.
I'm nearly there in that I have the query, and it works, but there is too much detail so I want to collapse the steps into each job-run, but I have no way of grouping the job-runs.
In the MSDB system database there are these useful tables:
sysjobs
sysjobhistory
This is the query so far. To reproduce this, replace the GUID with the GUID of a job on your SQL Agent - which you can find in [msdb].[sysjobs] table. It makes the problem clearer if you pick a job with more than 1 step.
DECLARE @job_id uniqueidentifier
Set @job_id = '0F70F5A1-C169-4CB4-99D8-A16FC2ED32A0'
SELECT sJOBH.job_id, sJOB.name AS job_title, sJOB.description, sJOBH.step_id, sJOBH.step_name, CASE WHEN [sJOBH].[run_date] IS NULL OR
[sJOBH].[run_time] IS NULL THEN NULL ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8))
+ ' ' + STUFF(STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) END AS RunDateTime,
STUFF(STUFF(RIGHT('000000' + CAST(sJOBH.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [RunDuration (HH:MM:SS)],
CASE [sJOBH].[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'Running' END AS LastRunStatus,
sJOBH.retries_attempted, sJOB.date_modified AS last_modified, sJOBH.message
FROM sysjobhistory AS sJOBH INNER JOIN
sysjobs AS sJOB ON sJOBH.job_id = sJOB.job_id
WHERE (sJOB.enabled = 1)
AND (sJOBH.job_id = @job_id)
ORDER BY job_title, RunDateTime, sJOBH.step_id
I picked up this next query (from SQL Profiler) which appears to drive the SQL Agent job history screen in SSMS. It doesn't make it any clearer how to group the steps of a job into a single "session" i.e. for a particular job there are 9 steps in a job, step number 0 is the job level, the 9 steps that happen just after that are all part of that job.
Note you also need to replace the GUID of this query with one from your system.
declare @tmp_sp_help_jobhistory table
(
instance_id int null,
job_id uniqueidentifier null,
job_name sysname null,
step_id int null,
step_name sysname null,
sql_message_id int null,
sql_severity int null,
message nvarchar(4000) null,
run_status int null,
run_date int null,
run_time int null,
run_duration int null,
operator_emailed sysname null,
operator_netsent sysname null,
operator_paged sysname null,
retries_attempted int null,
server sysname null
)
insert into @tmp_sp_help_jobhistory
exec msdb.dbo.sp_help_jobhistory
@job_id = '0f70f5a1-c169-4cb4-99d8-a16fc2ed32a0',
@mode='FULL'
SELECT *,
tshj.instance_id AS [InstanceID],
tshj.sql_message_id AS [SqlMessageID],
tshj.message AS [Message],
tshj.step_id AS [StepID],
tshj.step_name AS [StepName],
tshj.sql_severity AS [SqlSeverity],
tshj.job_id AS [JobID],
tshj.job_name AS [JobName],
tshj.run_status AS [RunStatus],
CASE tshj.run_date WHEN 0 THEN NULL ELSE
convert(datetime,
stuff(stuff(cast(tshj.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +
stuff(stuff(substring(cast(1000000 + tshj.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),
120) END AS [RunDate],
tshj.run_duration AS [RunDuration],
tshj.operator_emailed AS [OperatorEmailed],
tshj.operator_netsent AS [OperatorNetsent],
tshj.operator_paged AS [OperatorPaged],
tshj.retries_attempted AS [RetriesAttempted],
tshj.server AS [Server],
getdate() as [CurrentDate]
FROM @tmp_sp_help_jobhistory as tshj
ORDER BY [InstanceID] ASC
In the SSMS job history, it collapses each job run into the job level, and you can drill-down to see all the steps. This is the effect I want to reproduce.
Without using a loop or a cursor (coz they are evil!), how is this done? They must have done this in code because there is no clear way to group the results in the dataset.
There's an instance ID, but it varies for every step. There's a run-date, which would be fine if they were all daily jobs but some run more than once a day.
Sorry for the long intro but basically I think I want to create another column that will group one run of all the steps of a job together, which I could then use to group in the report. Unless there's another option I'm missing. Any ideas? Thanks 🙂
February 4, 2013 at 6:31 am
Following code works, i use it in RS report for our helpdesk so they can monitor critical jobs running on different servers, basicaly they want to see job history for the day and how each step manage, of course i neded job execution ID for grouping...
declare
@job uniqueidentifier
create table #job
(
IDint identity (1,1),
JobExecutionIDint null,
job_namevarchar(512),
step_startdatetime,
step_enddatetime null,
run_durationint,
status_idint,
status_namevarchar(512),
step_idint,
step_namevarchar(512),
sql_messagevarchar(max)
)
insert #job
select
null,
j.name as job_name,
CONVERT(DATETIME, RTRIM(h.run_date)) +
(h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4 [occur],
null,
cast(RIGHT('000000' + CONVERT(varchar(6), h.run_duration), 6) as int) [run_duration] ,
h.run_status,
case h.run_status when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled' end [Status]
,h.step_id
,h.step_name
,h.message
from msdb.dbo.sysjobhistory h (nolock)
inner join msdb.dbo.sysjobs j (nolock) on h.job_id = j.job_id
where j.job_id = @job
and h.run_date = convert(varchar, getdate(), 112)
--and h.step_id > 0
order by h.run_time, h.step_id
update #job
set step_end = dateadd(second, run_duration, step_start)
declare
@row int,
@step_id int,
@last_id int,
@execution_id int
set @row = 1
set @execution_id = 1
set @last_id = -1
DECLARE #cur INSENSITIVE CURSOR FOR
select step_id from #job
order by step_start, step_id
OPEN #cur
FETCH #cur into @step_id
WHILE @@FETCH_STATUS = 0
BEGIN
if @step_id >= @last_id
begin
update #job
set JobExecutionID = @execution_id
where ID = @row
end
else
begin
set @execution_id = @execution_id + 1
update #job
set JobExecutionID = @execution_id
where ID = @row
end
set @row = @row + 1
set @last_id = @step_id
fetch next from #cur into @step_id
end
close #cur
deallocate #cur
select * from #job
drop table #job
PS. i know it's a bit late 🙂 but i didn't find any solution anywhere.
February 4, 2013 at 3:59 pm
Thanks krysys for the suggestion.
I ended up using a row_number() over (partition by) construct to achieve what I needed, I should have followed up.
Thanks though.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply