SQL Scheduled job query, duration of last runs?

Question

Previously used this SQL Agent Jobs, how to document to get information about all SQL Scheduled jobs.

How can I find out the duration of the last run for each job? I need seconds, minutes, and hours (hopefully not, but I'm afraid).

Can anyone give some insight into how I could query this?

Pendragon

Answer 1

Assuming you're not going to have any jobs that run longer than 999 hours, this should give you a good starting point:

SELECT
    j.name,
    h.run_status,
    durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
        ' ','0'),4,0,':'),7,0,':'),
    [start_date] = CONVERT(DATETIME, RTRIM(run_date) + ' '
        + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),
        ' ','0'),3,0,':'),6,0,':'))
FROM
    msdb.dbo.sysjobs AS j
INNER JOIN
    (
        SELECT job_id, instance_id = MAX(instance_id)
            FROM msdb.dbo.sysjobhistory
            GROUP BY job_id
    ) AS l
    ON j.job_id = l.job_id
INNER JOIN
    msdb.dbo.sysjobhistory AS h
    ON h.job_id = l.job_id
    AND h.instance_id = l.instance_id
ORDER BY
    CONVERT(INT, h.run_duration) DESC,
    [start_date] DESC;

Evolution

Beautiful! But what does the run_status mean? – Pendragon

@Pendragon Per learn.microsoft.com/en-us/sql/relational-databases/… 0 = Failed 1 = Succeeded 2 = Retry 3 = Canceled 4 = In Progress – Brocatel

Answer 2

Aaron, I've made some tweaks to your script, which include the next scheduled run time (start date is the initial start date of the schedule, not necessarily the next start date for the job), and some various other checks. Thanks for the framework though; very helpful starting point indeed!

I also took some logic from an oldie but goodie (here).

USE msdb
Go


SELECT j.Name AS 'Job Name', 
    '"' + NULLIF(j.Description, 'No description available.') + '"' AS 'Description',
    SUSER_SNAME(j.owner_sid) AS 'Job Owner',
    (SELECT COUNT(step_id) FROM dbo.sysjobsteps WHERE job_id = j.job_id) AS 'Number of Steps',
    (SELECT COUNT(step_id) FROM dbo.sysjobsteps WHERE job_id = j.job_id AND command LIKE '%xp_cmdshell%') AS 'has_xpcmdshell',
    (SELECT COUNT(step_id) FROM dbo.sysjobsteps WHERE job_id = j.job_id AND command LIKE '%msdb%job%') AS 'has_jobstartstopupdate',
    (SELECT COUNT(step_id) FROM dbo.sysjobsteps WHERE job_id = j.job_id AND command LIKE '%ftp%') AS 'has_ftp',
    'Job Enabled' = CASE j.Enabled
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
    END,
    'Frequency' = CASE s.freq_type
        WHEN 1 THEN 'Once'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly relative'
        WHEN 64 THEN 'When SQLServer Agent starts'
    END, 
    CASE(s.freq_subday_interval)
        WHEN 0 THEN 'Once'
        ELSE cast('Every ' 
                + right(s.freq_subday_interval,2) 
                + ' '
                +     CASE(s.freq_subday_type)
                            WHEN 1 THEN 'Once'
                            WHEN 4 THEN 'Minutes'
                            WHEN 8 THEN 'Hours'
                        END as char(16))
    END as 'Subday Frequency',
    'Next Start Date'= CONVERT(DATETIME, RTRIM(NULLIF(js.next_run_date, 0)) + ' '
        + STUFF(STUFF(REPLACE(STR(RTRIM(js.next_run_time),6,0),
        ' ','0'),3,0,':'),6,0,':')),
    'Max Duration' = STUFF(STUFF(REPLACE(STR(maxdur.run_duration,7,0),
        ' ','0'),4,0,':'),7,0,':'),
    'Last Run Duration' = STUFF(STUFF(REPLACE(STR(lastrun.run_duration,7,0),
        ' ','0'),4,0,':'),7,0,':'),
    'Last Start Date' = CONVERT(DATETIME, RTRIM(lastrun.run_date) + ' '
        + STUFF(STUFF(REPLACE(STR(RTRIM(lastrun.run_time),6,0),
        ' ','0'),3,0,':'),6,0,':')),
    'Last Run Message' = lastrun.message
FROM dbo.sysjobs j
LEFT OUTER JOIN dbo.sysjobschedules js
    ON j.job_id = js.job_id
LEFT OUTER JOIN dbo.sysschedules s
    ON js.schedule_id = s.schedule_id 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
        FROM dbo.sysjobhistory
        GROUP BY job_id) maxdur
ON j.job_id = maxdur.job_id
-- INNER JOIN -- Swap Join Types if you don't want to include jobs that have never run
LEFT OUTER JOIN
    (SELECT j1.job_id, j1.run_duration, j1.run_date, j1.run_time, j1.message
    FROM dbo.sysjobhistory j1
    WHERE instance_id = (SELECT MAX(instance_id) 
                         FROM dbo.sysjobhistory j2 
                         WHERE j2.job_id = j1.job_id)) lastrun
    ON j.job_id = lastrun.job_id
ORDER BY [Job Name]

Shrew