1

Is it possible to find jobs that succeeded, but that had steps that failed like the desired output shows? I'm new to this side of development and I'm not sure about all of the system tables, etc. If it is possible, what tables would you use to construct the query?

I'm not sure where to post 'is this possible' type questions. If this is the wrong place, just let me know and I can take it down. Thanks for your help.

Desired Output

enter image description here

3

You need to analyze data in the jobstephistory table.

You can use following query, it is slightly modified script from Analyzing SQL Agent Job and Job Step History in SQL Server by Atif Shehzad:

--Script # 1: To generate steps history of all jobs USE msdb GO

SELECT j.name JobName,h.step_name StepName,
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 StepExecutionStatus, 
case jobsts.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 JobExecutionStatus, 
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id and h.step_id !=0
inner join sysjobhistory jobsts
on j.job_id = jobsts.job_id and jobsts.step_id =0
WHERE 
jobsts.run_status = 1
and h.run_status != 1
ORDER BY j.name, h.run_date, h.run_time
GO
0

The answer I put together was mostly from Querying SQL Server Agent Job Information by Dattatrey Sindol. I think part of Piotr's answer was close, but didn't exactly meet my requirements as the code below does. Thanks Piotr!

The final select joins both tables and has a lot of columns, but it shows the step number, etc. that failed.

The answer below met my needs.

IF OBJECT_ID ('tempdb..#Jobs') IS NOT NULL DROP TABLE #Jobs;
IF OBJECT_ID ('tempdb..#Steps') IS NOT NULL DROP TABLE #Steps;

SELECT      [sJOB].[job_id]                                                                                                                                                                                                                 AS [JobID]
          , [sJOB].[name]                                                                                                                                                                                                                   AS [JobName]
          , 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 [JobLastRunDateTime]
          , CASE [sJOBSCH].[NextRunDate] WHEN 0 THEN NULL ELSE CAST(CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) + ' ' + STUFF (STUFF (RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)END AS [JobNextRunDateTime]
          , sJOB.Enabled          
          , CASE [sJOBH].[run_status] WHEN 0 THEN 'Failed'
                WHEN 1 THEN 'Succeeded'
                WHEN 2 THEN 'Retry'
                WHEN 3 THEN 'Canceled'
                WHEN 4 THEN 'Running' -- In Progress
            END                                                                                                                                                                                                                             AS [JobLastRunStatus]
          , STUFF (STUFF (RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')                                                                                                                           AS [JobLastRunDuration (HH:MM:SS)]
          , [sJOBH].[message]                                                                                                                                                                                                              AS [JobLastRunStatusMessage]

INTO        #Jobs
FROM        [msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN   (
    SELECT      [job_id]
              , MIN ([next_run_date]) AS [NextRunDate]
              , MIN ([next_run_time]) AS [NextRunTime]
    FROM        [msdb].[dbo].[sysjobschedules]
    GROUP BY    [job_id]
)                                  AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN   (
    SELECT  [job_id]
          , [run_date]
          , [run_time]
          , [run_status]
          , [run_duration]
          , [message]
          , ROW_NUMBER () OVER (PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC) AS RowNumber
    FROM    [msdb].[dbo].[sysjobhistory]
    WHERE   [step_id] = 0
)                                  AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id]
                                                 AND [sJOBH].[RowNumber] = 1
--WHERE       sJOB.job_id = ''
ORDER BY    [JobName];


--=============================================================================================

SELECT      [sJOB].[job_id]                                                                                                                                                                                                                 AS [JobID]
          , [sJOB].[name]                                                                                                                                                                                                                   AS [JobName]
          , CASE [sJSTP].[last_run_outcome] WHEN 0 THEN 'Failed'
                WHEN 1 THEN 'Succeeded'
                WHEN 2 THEN 'Retry'
                WHEN 3 THEN 'Canceled'
                WHEN 5 THEN 'Unknown' END                                                                                                                                                                                                   AS [StepLastRunStatus]
          , [sJSTP].[step_uid]                                                                                                                                                                                                              AS [StepID]
          , [sJSTP].[step_id]                                                                                                                                                                                                               AS [StepNo]
          , [sJSTP].[step_name]                                                                                                                                                                                                             AS [StepName]
          , STUFF (STUFF (RIGHT('000000' + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')                                                                                                                      AS [StepLastRunDuration (HH:MM:SS)]
          , [sJSTP].[last_run_retries]                                                                                                                                                                                                      AS [StepLastRunRetryAttempts]
          , CASE [sJSTP].[last_run_date] WHEN 0 THEN NULL ELSE CAST(CAST([sJSTP].[last_run_date] AS CHAR(8)) + ' ' + STUFF (STUFF (RIGHT('000000' + CAST([sJSTP].[last_run_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)END AS [StepLastRunDateTime]
INTO        #Steps
FROM        [msdb].[dbo].[sysjobsteps] AS [sJSTP]
INNER JOIN  [msdb].[dbo].[sysjobs]     AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id]
--WHERE       sJOB.job_id = ''
ORDER BY    [JobName]
          , [StepNo];


SELECT              *
FROM                #Jobs  AS j
RIGHT OUTER JOIN    #Steps AS s ON s.JobID = j.JobID
WHERE (j.JobLastRunStatus = 'Succeeded' AND s.StepLastRunStatus = 'Failed')
ORDER BY j.JobLastRunDateTime DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.