Need better query to capture job runs on sql servers

  • I created an SSIS job that pulls in details of the most recent sql job runs on other

    sql servers (sql 2008, sql 2005). It works fine but I'm looking for a "smarter" query approach. Here's what I'm using now:

    Select * from

    (

    SELECTtop 1

    sjh.[server],

    sj.name as JobName,

    sjh.run_status as RunStatus,

    sjh.run_date as RunDate

    FROM msdb.dbo.sysjobhistory AS sjh INNER JOIN msdb.dbo.sysjobs AS sj

    ON (sjh.job_id = sj.job_id)

    where sj.name like ('%Job1%')

    order by sjh.run_date desc

    ) as T1

    union

    Select * from

    (

    SELECTtop 1

    sjh.[server],

    sj.name as JobName,

    sjh.run_status as RunStatus,

    sjh.run_date as RunDate

    FROM msdb.dbo.sysjobhistory AS sjh INNER JOIN msdb.dbo.sysjobs AS sj

    ON (sjh.job_id = sj.job_id)

    where sj.name like ('%Job2%')

    order by sjh.run_date desc

    ) as T2

    Suppose a sql server has 20 jobs -- I would need 20 unions! There must be a better way to write this query.

    TIA,

    edm2

  • This is more dynamic (and includes run time). Depending on your requirements, you might consider changing to use outer joins on sysjobhistory subqueries to catch any jobs which haven't processed or have no recent history.

    SELECTsjh.server

    ,sj.name

    ,sjh.run_status

    --,sjh.run_date

    ,run_date = CAST

    (

    CONVERT(VARCHAR, sjh.run_date)

    + ' ' + STUFF(STUFF(RIGHT('000000'

    + CONVERT(VARCHAR,sjh.run_time),6),5,0,':'),3,0,':')

    AS DATETIME

    )

    FROMmsdb.dbo.sysjobs sj

    INNER JOIN(

    SELECTDISTINCT

    job_id

    ,server

    ,run_status

    ,run_date

    ,run_time

    ,run_duration

    FROMmsdb.dbo.sysjobhistory

    ) sjh ON sjh.job_id = sj.job_id

    INNER JOIN (

    SELECTjob_id

    ,run_date = MAX(run_date)

    ,run_time = MAX(run_time)

    FROMmsdb.dbo.sysjobhistory

    GROUP BY job_id

    ) mx ON mx.job_id = sjh.job_id

    AND mx.run_date = sjh.run_date

    AND mx.run_time = sjh.run_time

    ORDER BY sjh.run_date;

    _____________________________________________________________________
    - Nate

    @nate_hughes
  • What about jobs that run more than once a day? For example, I have a dozen jobs on some servers that run once per minute. I don't want to know the status of just the latest run. I want to know the status of all the runs in the last 24 hours.

    Would that be something that you're interested in?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    You are absolutely right. My query will be used to create a report for the Operators group to check the daily sql runs jobs. I too have some jobs that run every minute (related to ASP.NET state) but I didn't want to overburden them with that much info and I will be alerted for those jobs anyway. I guess that's why I did not include anything but the most recent run.

    BD

  • Barkingdog (6/11/2015)


    Jeff,

    You are absolutely right. My query will be used to create a report for the Operators group to check the daily sql runs jobs. I too have some jobs that run every minute (related to ASP.NET state) but I didn't want to overburden them with that much info and I will be alerted for those jobs anyway. I guess that's why I did not include anything but the most recent run.

    BD

    If you aggregate the information to just one line per job with the total number of runs, failures, retries, and cancelations along with some key information like the job name, last run date/time and next run date/time along with an indication of what is currently running and how long it's been running, you'll end up with a 20 line report that tells you virtually everything. The people that have audited us absolutely love it and, since it's automatically mailed to those that need to know (including myself), there's no chance of something slipping by. Yes, I agree that alerts help but if you have multiple failures of the same job, it's nice to have something that reports that without having to count alert emails... especially on the once a minute things when they go haywire.

    Basically, it's just one query with no unions and it will auto-magically adjust as new jobs are added or jobs are deleted. I even have markers of the "one time" jobs that change color if something hasn't been run for a week and then again for a month. It also tells me if the job and the schedule are actually enabled.

    The guts of it all are the same as one of your unioned queries but without the WHERE clause. You just need to tweak what you've got.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    Thank you for the perspective. I like it and will remove the WHERE clause as you suggested.

    BD

  • Barkingdog (6/12/2015)


    Jeff,

    Thank you for the perspective. I like it and will remove the WHERE clause as you suggested.

    BD

    We'll need to do a bit more than just removing the WHERE clause. Heh... if you need help, bark! 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply