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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Job Activity Monitor view - but where are the...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Wee-Lin Lim

Posted Friday, July 18, 2008 4:54 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 9:12 AM
Points: 16, Visits: 50

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

Post #536625

vidhya sagar

Posted Friday, July 18, 2008 5:16 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 2:45 AM
Points: 416, Visits: 521

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)

Regards..Vidhya Sagar
SQL-Articles

Post #536636

Wee-Lin Lim

Posted Friday, July 18, 2008 5:42 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 9:12 AM
Points: 16, Visits: 50

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

Post #536657

chinn

Posted Wednesday, January 02, 2013 8:29 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 127, Visits: 494

Hello Wee-Lin,
Did you happen to find resolution to our problem i am curious about the same thing?

Thanks,

Post #1402154

Bhuvnesh

Posted Thursday, January 03, 2013 4:06 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

4 years old thread

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1402281

« Prev Topic | Next Topic »

Permissions