SQL Scheduled Job Performance

Question

Post reply

SQL Scheduled Job Performance

rogue1009

Old Hand

Points: 365
More actions
November 8, 2022 at 10:18 am

#4109980

I am currently trying to write a query that tells me when a SQL agent job ran last and also how long it took. This is my code currently
with cte2
As (select j.name as 'JobName',
j.job_id as 'JobID',
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
run_duration as 'RunDurationSeconds'
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
where j.enabled = 1 --Only Enabled Jobs
--and j.name = 'TestJob' --Uncomment to search for a single job
)
Select cte.JobName,
cte.JobID,
--RunDateTime as [most recent run],
max (cte.RunDateTime) as [most recent run],
cte.RunDurationSeconds
from cte2 as cte
where
1=1
--and RunDateTime =
--(
-- Select max(rundatetime) from cte2
--)
Group by
cte.JobName,
cte.JobID,
cte.RunDurationSeconds

The MAX does not seem to be running and is bringing back the time it took over different days. When I uncomment the sub query in the where clause it just returns the latest job that ran.
What do I need to do to the query to bring back
Job name
Job id
Latest date/time it ran
how long that took

March 20, 2023 at 8:40 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

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

Here's what you asked for, along with a last run status...

   WITH
cteJob AS
(--==== Get only the jobs that are enabled
 SELECT job_id ,name
   FROM msdb.dbo.sysjobs job
  WHERE job.enabled = 1 --Only jobs that are enabled
)
,cteLatest AS
(--==== Setup to pick only the latest history row for each job.  SortOrder = 1 for latest run for each job_id.
 SELECT  SortOrder = ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY run_date DESC, run_time DESC)
        ,job_id, run_status, run_date, run_time, run_duration
   FROM msdb.dbo.sysjobhistory
  WHERE step_id = 0 --Only the top level reporting for each job
)--==== Join the two CTEs to produce the desired report with a RunStatus added.
 SELECT  JobName            = j.name
        ,JobID              = j.job_id
        ,RunStatus          = CASE l.run_status --I don't use CHOOSE because it's slower than CASE 
                              WHEN 1 THEN 'Succeeded' --Short Ciruit the CASE because most will have a run_status = 1
                              WHEN 0 THEN 'Failed'
                              WHEN 2 THEN 'Retry'
                              WHEN 3 THEN 'Cancelled'
                              WHEN 4 THEN 'In Progress'
                              ELSE 'UNKNOWN'
                              END
        ,RunDateTime        = msdb.dbo.agent_datetime(l.run_date,l.run_time)
        ,RunDurationSeconds = l.run_duration/10000*3600+l.run_duration/100%100*60+l.run_duration%100
   FROM cteJob j
   JOIN cteLatest l ON l.job_id = j.job_id
  WHERE l.SortOrder = 1 --Only the latest run
  ORDER BY JobName
;

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