查找成功但有步驟失敗的作業

是否有可能找到成功的工作，但其中的步驟失敗，如所需的輸出所示？我是這方面的開發新手，我不確定所有系統表等。如果可能，您將使用哪些表來構造查詢？

我不確定在哪裡發布“這可能”類型的問題。如果這是錯誤的地方，請告訴我，我可以將其刪除。謝謝你的幫助。

期望的輸出

您需要分析 jobstephistory 表中的數據。

您可以使用以下查詢，它是來自Atif Shehzad 的 Analyzing SQL Agent Job and Job Step History in SQL Server的稍微修改的腳本：

--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

我匯總的答案主要來自Dattatrey Sindol 的 Querying SQL Server Agent Job Information。我認為 Piotr 的部分答案很接近，但不像下面的程式碼那樣完全符合我的要求。謝謝彼得！

最終的選擇連接了兩個表並且有很多列，但是它顯示了失敗的步驟號等。

下面的答案滿足了我的需求。

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

引用自：https://dba.stackexchange.com/questions/253849