SQL SERVER查询Job每个步骤执行结果情况

时间:2022-02-12 22:19:19
SELECT
    [job].[job_id],
    [job].[name] AS 'job_name',
    [jobstep].[step_id],
    [jobstep].[step_name],
    [jobhis].[message] AS 'err_mess'--步骤失败的原因
FROM [dbo].[sysjobs] AS job WITH(NOLOCK)
INNER JOIN dbo.[sysjobhistory] AS jobhis WITH(NOLOCK)--run_status=0代表失败步骤
        ON ([job].[job_id] = [jobhis].[job_id] AND [jobhis].[run_status] = 0)
INNER JOIN [dbo].[sysjobsteps] AS jobstep WITH(NOLOCK)
        ON ([job].[job_id] = [jobstep].[job_id] AND [jobstep].[step_id] = [jobhis].[step_id])
        --run_date是步骤执行的时间
WHERE [jobhis].[run_date] = CONVERT(INT, CONVERT(NVARCHAR(8), DATEADD(dd, -1, GETDATE()), 112))