October 28, 2011 at 6:57 am
I need to create a report about the SQL Agent Jobs. The information I need is the Job name, Status (Execute or Idle), Last run time, Last job status (Failed or Succeeded) and Durastion.
I have found everything so far in the sysjobs and sysjobhistory but I can not find the Status (Executing or Idle).
Is there a table that contains this information?
Thanks
Scott
October 28, 2011 at 7:19 am
Try sysjobactivity.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2011 at 8:37 am
This may give you some help the where constraints filters for running, you might not need the jon for categories I had this into filter out replication jobs in another script:
SELECT j.name, j.enabled, DATEDIFF(minute,start_execution_date, getdate()) AS CurrentDurationInMins
FROM msdb..sysjobs j
JOIN msdb..sysjobactivity a
ON j.job_id = a.job_id
JOIN msdb..syscategories c
ON j.category_id = c.category_id
where start_execution_date IS NOT NULL
AND stop_execution_date IS NULL
MCITP SQL 2005, MCSA SQL 2012
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply