Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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,, 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 = '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...

share|improve this question
Regarding your edit, if CROSS APPLY worked on SQL Server 2000, you might be able to do this easily with a table-valued function. Unfortunately there aren't many modern solutions if you're going to stick to a platform from the dark ages. :-) – Aaron Bertrand Feb 16 '12 at 2:20
up vote 2 down vote accepted

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,, t.step_name, t.run_date, t.run_time,
       t.step_id, t.run_datetime
FROM #t AS t
      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
      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
    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;

share|improve this answer
Nice idea with the temp table, but wow, that's seriously annoying to have to do all that. Would it make it any easier if we added an identity field to #t, and did the INSERT using an ORDER BY? Then we could just do a MAX(rowID). I guess that probably doesn't accomplish anything different than your MAX(run_datetime) – BradC Feb 15 '12 at 23:19
I don't think you'll get a different or faster answer, and it will probably net out to be the same amount of code (since you'll have to add a CREATE TABLE statement), it's just a different way to do it. You're welcome to try but you already have an answer that works. :-) – Aaron Bertrand Feb 15 '12 at 23:41
Thanks, Aaron. BTW, your date conversion function fails when run_time < 1000. Fixed by padding extra zeros. – BradC Mar 1 '12 at 22:08

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.enabled
    , h1.last_row
    , h2.step_id, h2.step_name, h2.run_date, h2.run_time, h2.run_duration
FROM msdb.dbo.sysjobs j
    (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

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.