Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to return Jobs that are in Progress Expand / Collapse
Author
Message
Posted Saturday, March 8, 2014 9:04 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:44 PM
Points: 4,236, Visits: 4,284
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/

Post #1548971
Posted Saturday, March 8, 2014 3:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 3,931, Visits: 7,158
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; They'll drag you down to their level and beat you with experience"
Post #1549002
Posted Saturday, March 8, 2014 5:26 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:44 PM
Points: 4,236, Visits: 4,284
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/

Post #1549014
Posted Sunday, March 9, 2014 9:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1549058
Posted Sunday, March 9, 2014 11:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:44 PM
Points: 4,236, Visits: 4,284
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/

Post #1549067
Posted Sunday, March 9, 2014 12:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:11 PM
Points: 6,604, Visits: 8,910
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1549069
Posted Sunday, March 9, 2014 2:27 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 3,931, Visits: 7,158
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; They'll drag you down to their level and beat you with experience"
Post #1549080
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse