July 18, 2008 at 4:54 am
Hello all,
I wrote a view that joins the sysjob, sysjobactivity, sysjobhistory and syscategories tables together to give me a resultset that looks like the Job Activity Monitor. I wanted this so I could retrieve a specific resultset to log the status of certain jobs daily, added to other daily checks to provide a dashbpard report......
Anyway, I have copied the view below. However I am finding that the view does not return any jobs that are currently running, and shown in the job Activity Monitor with a Status of "executing", which I assume is equivalent to a run_status of 4 (in progress). When I search the sysjobactivity and sysjobhistory I cannot see the row.
Does anyone have any idea what is wrong with my view or where this row resides?
Thanks!
Weelin
ps - the RANK is done so taht I return only the latest Job Activity, but even when I search without the RANK I cannot find it.
WITH latest_job_activity AS
(
SELECT *
FROM
(
SELECT job_id, start_execution_date, next_scheduled_run_date, job_history_id, RANK()
OVER (Partition By job_id ORDER BY start_execution_date DESC) AS Rank
FROM msdb..sysjobactivity
) tmp
WHERE Rank = 1
)
SELECT DISTINCT sj.[name] AS 'JobName',
CASE [enabled]
WHEN 1 THEN 'Enabled'
ELSE 'Disabled'
END AS 'Enabled',
CASE sjh.[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
ELSE 'In Progress'
END AS 'RunStatus' ,
sjh.[run_status] AS 'RunStatusNumber',
start_execution_date AS 'LastRun',
--Last Run Duration HHMMSS Converted to 00:00:00
STUFF(STUFF(REPLACE(STR(run_duration,6),' ','0'),5,0,':'),3,0,':')
AS 'Duration',
next_scheduled_run_date AS 'NextRun',
sc.name AS 'Category'
-- From Joined System Tables: Sysjobs, Sysjobschedules, Sysjobhistory, syscategories.
FROMmsdb..sysjobs sj
JOINlatest_job_activity lja
ONsj.job_id = lja.job_id
JOINmsdb..sysjobhistory sjh
ONlja.job_history_id = sjh.instance_id
JOINmsdb..syscategories sc
ONsj.category_id = sc.category_id
WHERE1=1
July 18, 2008 at 5:16 am
[font="Verdana"]Why can you try the below procedures in msdb Database.
sp_help_jobactivity (Transact-SQL)
sp_help_jobhistory (Transact-SQL)[/font]
Regards..Vidhya Sagar
SQL-Articles
July 18, 2008 at 5:42 am
Hi, thanks for the reply.
The view gives me information on the category and also joins jobactivity to jobhistory to try and give me the run_status of the CURRENT/LATEST job. Individually, the system SPs do not give the information together, which is what I require returned in my RS report as a dataset. Also, looking at sp_help_jobhistory, it still does not give me the current run_status of In_Progress - this is still missing when you run sp_help_jobhistory.
Another reason I use a view and then select from it is because I have a reference table that holds the job names that I want to look at, which I concatenate together to create an IN statement to filter the resultset.
Cheers
Weelin
January 2, 2013 at 8:29 pm
Hello Wee-Lin,
Did you happen to find resolution to our problem i am curious about the same thing?
Thanks,
January 3, 2013 at 4:06 am
4 years old thread
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 17, 2019 at 2:49 am
Same issue (5 MORE years on!) sysjobhistory does not appear to ever contain any rows with run_status 4 (in progress), despite the documentation... (checked on SQL2014 & SQL2017). Even tried with a dummy Job-Step 1 as some posts suggested but still no run_status=4 records in msdb.dbo.sysjobhistory.
I'm surprised I couldn't find a more recent post regarding this issue.
May 17, 2019 at 5:10 pm
Some of it is undocumented. The job activity monitory executes several things, collects data into temp tables to eventually get the view you see in SSMS.
Ways to get currently executing jobs would include
msdb.dbo.sp_get_composite_job_info shows currently executing jobs - where current execution status = 1.
You could also use xp_sqlagent_enum_jobs 1, 'JobOwner'
If you look at the code for sp_help_job, you can follow the path for how some of the information is obtained.
Sue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply