CODEWITHSUNDEEP
sqlsql-serversql-server-agent
neeko
neeko

Reputation: 2000

How to get the last run job details in SQL

How to get the last run job details in SQL Server Agent using SQL including the step details only for the last run job (not the job outcome) as i want to display this in an application

Please help been stuck on this for ages

This is the code I have been using below, this brings back all steps for all Jobs in the Job History,

However, I just want to see the steps from the last run job

thanks

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 'Succeeded' 
    when 2 then 'Retry' 
    when 3 then 'Cancelled' 
    when 4 then 'In Progress' 
end as ExecutionStatus, 
h.message MessageGenerated
FROM sysjobhistory h 
inner join sysjobs j
ON j.job_id = h.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 j.[job_id] = [sJOBH].[job_id]
            AND [sJOBH].[RowNumber] = 1

where j.job_id = 'F04E5D3B-C873-448A-805C-C6309A92DAEC'

ORDER BY j.name, h.run_date, h.run_time desc
GO

Upvotes: 3

Views: 23953

Answers (1)

deeg
deeg

Reputation: 528

Hopefully this helps,

Additional join to msdb.dbo.sysjobactivity shows you the start/end time at a job level. Using this range you can specify to only show you the job steps for the most recent run. 

    DECLARE @job_id UNIQUEIDENTIFIER 
            ,@job_name  VARCHAR(256)

    SET @job_id = 'DF4C9555-5B24-4649-97CE-5708C53F762C'
    SET @job_name = 'syspolicy_purge_history'

    --search for job_id if none was provided
    SELECT  @job_id = COALESCE(@job_id,job_id)
    FROM    msdb.dbo.sysjobs 
    WHERE   name = @job_name

    SELECT  t2.instance_id
            ,t1.name as JobName
            ,t2.step_id as StepID
            ,t2.step_name as StepName
            ,CONVERT(CHAR(10), CAST(STR(t2.run_date,8, 0) AS DATETIME), 111) as RunDate
            ,STUFF(STUFF(RIGHT('000000' + CAST ( t2.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') as RunTime
            ,t2.run_duration
            ,CASE t2.run_status WHEN 0 THEN 'Failed'
                                WHEN 1 THEN 'Succeeded' 
                                WHEN 2 THEN 'Retry' 
                                WHEN 3 THEN 'Cancelled' 
                                WHEN 4 THEN 'In Progress' 
                                END as ExecutionStatus
            ,t2.message as MessageGenerated    
    FROM    msdb.dbo.sysjobs t1
    JOIN    msdb.dbo.sysjobhistory t2
            ON t1.job_id = t2.job_id   
            --Join to pull most recent job activity per job, not job step
    JOIN    (
            SELECT  TOP 1
                    t1.job_id
                    ,t1.start_execution_date
                    ,t1.stop_execution_date
            FROM    msdb.dbo.sysjobactivity t1
            --If no job_id detected, return last run job
            WHERE   t1.job_id = COALESCE(@job_id,t1.job_id)
            ORDER 
            BY      last_executed_step_date DESC
            ) t3
            --Filter on the most recent job_id
            ON t1.job_id = t3.job_Id
            --Filter out job steps that do not fall between start_execution_date and stop_execution_date
            AND CONVERT(DATETIME, CONVERT(CHAR(8), t2.run_date, 112) + ' ' 
            + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), t2.run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)  
            BETWEEN t3.start_execution_date AND t3.stop_execution_date

Edit: Added two parameters to the top, @job_id and @job_name. If @job_id is not provided, first it'll attempt to determine the job_id of the @job_name provided, and return the last run job is no values are provided.

Upvotes: 7

Related Questions