Need better query to capture job runs on sql servers

Question

Need better query to capture job runs on sql servers

Barkingdog

SSCoach

Points: 18670
More actions
June 11, 2015 at 9:03 am

#297807

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

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

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

RP_DBA SSCertifiable Points: 5116 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 2

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)

Barkingdog SSCoach Points: 18670 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

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)

Barkingdog SSCoach Points: 18670 More actions · Answer 5

Jeff,

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

BD

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 6

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)