Get the most recently completed step for each SQL job

Question

I'm trying to get a list of jobs and their most recently completed step from the MSDB database. For a single job, this is pretty straight forward, something like:

 SELECT TOP 1 j.job_id, j.name, h.step_name, h.run_date, h.run_time, h.step_id
 FROM msdb.dbo.sysjobs j
 INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
 WHERE j.name = 'my favorite job'
 ORDER BY h.run_date DESC, h.run_time DESC, h.step_id DESC

But how can I get a list of ALL jobs and their most recently completed step?

Note this is SQL 2000, so I can't use PARTITION OVER. I also can't do a SELECT MAX() because its not a single RunDateTime field, I have to sort descending by two fields (OrderDay, OrderTime).

Edit: Probably not possible, but I'd love to be able to something like:

SELECT j.job_id, ...
FROM msdb.dbo.sysjobs j
INNER JOIN (SELECT TOP 1 step_name, run_date, run_time, step_id
            FROM msdb.dbo.sysjobhistory
            WHERE job_id = j.job_id
            ORDER BY run_date desc, run_time desc, step_id desc) as H

or something, but it doesn't seem to like syntax like that...

Answer 1

Unfortunately because there can be ties (the second-to-last step can take 0 seconds and therefore finish at "exactly" the same time as the last step), this is really not pretty in SQL Server 2000. Rather than calculate all of the datetime stuff multiple times I think the easiest way is to stuff it into a #temp table. You may consider creating a permanent view with the datetime conversion.

SELECT job_id, step_name, run_date, run_time, step_id, 
    run_datetime = CONVERT(DATETIME, RTRIM(run_date) + ' ' + 
    STUFF(STUFF(RIGHT('00000' + RTRIM(run_time), 6),3,0,':'),6,0,':'))
  INTO #t
      FROM msdb.dbo.sysjobhistory;

SELECT j.job_id, j.name, t.step_name, t.run_date, t.run_time,
       t.step_id, t.run_datetime
FROM #t AS t
INNER JOIN 
    (
      SELECT job_id, run_datetime = MAX(run_datetime)
      FROM #t GROUP BY job_id
    ) AS m 
    ON m.job_id = t.job_id
    AND m.run_datetime = t.run_datetime
INNER JOIN 
    (
      SELECT job_id, run_datetime, step_id = MAX(step_id) 
      FROM #t GROUP BY job_id, run_datetime 
    ) AS s 
    ON s.job_id = m.job_id
    AND s.run_datetime = m.run_datetime
    AND s.step_id = t.step_id
INNER JOIN 
    msdb.dbo.sysjobs AS j
    ON t.job_id = j.job_id
    AND m.job_id = j.job_id
    AND s.job_id = j.job_id;

DROP TABLE #t;

Answer 2

I just noticed that sysjobhistory does have an identity key, "INSTANCE_ID". I don't know why I thought the table didn't have one...

Finding the largest INSTANCE_ID for each job (excluding step 0) makes this much easier, and avoids the temp table:

SELECT j.job_id, j.name, j.enabled
    , h1.last_row
    , h2.step_id, h2.step_name, h2.run_date, h2.run_time, h2.run_duration
FROM msdb.dbo.sysjobs j
LEFT OUTER JOIN 
    (SELECT job_id, max(instance_id) as last_row
     FROM msdb.dbo.sysjobhistory
     WHERE step_id > 0
     GROUP BY job_id) as h1
    ON j.job_id = h1.job_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h2
    ON h1.job_id = h2.job_id
    AND h1.last_row = h2.instance_id
ORDER BY j.name 

I still have to put the date/time conversion in there, but this seems to work.