Need to return Jobs that are in Progress

Question

Need to return Jobs that are in Progress

Welsh Corgi

SSC Guru

Points: 116520
More actions
March 8, 2014 at 9:04 am

#304915

Hi SQL Gurus :-),
I have a query that returns the jobs that have completed for the nightly processing.
If I run the query it does not reflect jobs that are in progress.
How can the following query be modified to reflect jobs that have started but have not completed?
Any input would be much appreciated. Thank you.
SELECT DISTINCT j.Name AS Job_Name,
msdb.dbo.Agent_datetime(Run_Date, Run_Time) AS Run_Datetime,
Dateadd(second, Datediff(second, 0, msdb.dbo.Agent_datetime(19000101, Run_Duration)), msdb.dbo.Agent_datetime(Run_Date, Run_Time)) AS Run_Endtime,
Stuff(Stuff(RIGHT('000000'
+ Cast(Run_Duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS Run_Duration,
CASE h.Run_Status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS Execution_Status
FROM msdb..Sysjobhistory h
INNER JOIN msdb..Sysjobs j
ON h.Job_id = j.Job_id
WHERE msdb.dbo.Agent_datetime(Run_Date, Run_Time) >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,CAST('18:00' AS DATETIME))
ORDER BY Run_Datetim
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 7 posts - 1 through 6 (of 6 total)

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

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 1

Include a JOIN to msdb.dbo.sysjobactivity and ORDER BY the run_requested_date DESC - it will return 0 for any job step that is currently executing - example: (SELECT TOP 1 ISNULL(last_executed_step_id, 0)

FROM msdb.dbo.sysjobactivity

WHERE job_id = @JobID

ORDER BY run_requested_date DESC )

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Thanks but can I get the entire code?

It does not work.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What doesn't work?

That's a derived table that Jessie posted, you'd put it in the join clause and join to it like a table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

GilaMonster (3/9/2014)
What doesn't work?
That's a derived table that Jessie posted, you'd put it in the join clause and join to it like a table.

I appreciate it. Could I get the entire syntax?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

WayneS SSC Guru Points: 95409 More actions · Answer 5

http://technet.microsoft.com/en-us/library/ms177634.aspx

FROM (derived_table_query) AS <derived_table_alias>

ON derived_table_alias.column = other_table.other_column

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 6

How about this? You could just both result sets into a temp table or use CTE to select out the results SELECT

ja.job_id,

j.name AS job_name,

ja.start_execution_date,

ISNULL(last_executed_step_id, 0) + 1 AS current_executed_step_id,

Js.step_name

FROM

msdb.dbo.sysjobactivity ja

LEFT JOIN msdb.dbo.sysjobhistory jh

ON ja.job_history_id = jh.instance_id

JOIN msdb.dbo.sysjobs j

ON ja.job_id = j.job_id

JOIN msdb.dbo.sysjobsteps js

ON ja.job_id = js.job_id

AND ISNULL(ja.last_executed_step_id, 0) + 1 = js.step_id

WHERE

ja.session_id = (SELECT TOP 1 session_id

FROM msdb.dbo.syssessions

ORDER BY agent_start_date DESC)

AND start_execution_date IS NOT NULL

AND stop_execution_date IS NULL;

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience