Job Activity Monitor view - but where are the In Progress jobs?

  • Wee-Lin Lim

    SSC Enthusiast

    Points: 146

    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.

    FROM msdb..sysjobs sj

    JOIN latest_job_activity lja

    ON sj.job_id = lja.job_id

    JOIN msdb..sysjobhistory sjh

    ON lja.job_history_id = sjh.instance_id

    JOIN msdb..syscategories sc

    ON sj.category_id = sc.category_id

    WHERE 1=1

  • Vidhya Sagar

    SSCrazy Eights

    Points: 8216

    [font="Verdana"]Why can you try the below procedures in msdb Database.

    sp_help_job (Transact-SQL)

    sp_help_jobactivity (Transact-SQL)

    sp_help_jobhistory (Transact-SQL)[/font]

  • Wee-Lin Lim

    SSC Enthusiast

    Points: 146

    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

  • chinn

    SSCrazy

    Points: 2840

    Hello Wee-Lin,

    Did you happen to find resolution to our problem i am curious about the same thing?

    Thanks,

  • Bhuvnesh

    SSC Guru

    Points: 59344

    4 years old thread

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Piquet

    SSCommitted

    Points: 1522

    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.

  • Sue_H

    SSC Guru

    Points: 90260

    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 7 (of 7 total)

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