Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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
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