Take the 2-minute tour ×
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. It's 100% free, no registration required.

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...

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
add comment

2 Answers

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, 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;
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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

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.