August 10, 2016 at 9:21 am
I've created the below query to retrieve some information on agent jobs scheduled which works fine and returns 33 rows. I'm trying to pull the next_scheduled_run_date from sysjobactivity, but whatever I try with JOINs on the 3rd table it returns more than 200 rows now (it's pulling back all rows where the job has ran rather than just the latest run). I just need the same data as original but with the next scheduled run date included also. It's probably really straightforward but I can't see the wood for the trees at the moment.
Use MSDB
Go
Select Name, [Enabled], run_date as [Last_Run_Date], Run_Status, Run_Duration, Date_Modified
FROM sysjobs A
OUTER APPLY (SELECT TOP 1 * FROM sysjobhistory B
WHERE A.job_id = B.job_id
ORDER BY B.run_date DESC) as B
Thanks in advance
August 10, 2016 at 9:55 am
TheFault (8/10/2016)
I've created the below query to retrieve some information on agent jobs scheduled which works fine and returns 33 rows. I'm trying to pull the next_scheduled_run_date from sysjobactivity, but whatever I try with JOINs on the 3rd table it returns more than 200 rows now (it's pulling back all rows where the job has ran rather than just the latest run). I just need the same data as original but with the next scheduled run date included also. It's probably really straightforward but I can't see the wood for the trees at the moment.Use MSDB
Go
Select Name, [Enabled], run_date as [Last_Run_Date], Run_Status, Run_Duration, Date_Modified
FROM sysjobs A
OUTER APPLY (SELECT TOP 1 * FROM sysjobhistory B
WHERE A.job_id = B.job_id
ORDER BY B.run_date DESC) as B
Thanks in advance
Seems to work OK for me, though I would suggest trying out the alternative method I show below too, for performance reasons:
USE msdb;
GO
SELECT A.name
, A.enabled
, B.run_date AS Last_Run_Date
, B.run_status
, B.run_duration
, A.date_modified
FROM dbo.sysjobs A
OUTER APPLY (SELECT TOP 1
B.run_date
, B.run_status
, B.run_duration
FROM dbo.sysjobhistory B
WHERE A.job_id = B.job_id
ORDER BY B.run_date DESC
) AS B;
WITH OrderedRunDates
AS (SELECT jh1.job_id
, rn = ROW_NUMBER() OVER (PARTITION BY jh1.job_id ORDER BY jh1.run_date DESC)
, jh1.run_date
, jh1.run_status
, jh1.run_duration
FROM dbo.sysjobhistory jh1
)
SELECT A.name
, A.enabled
, ord.run_date AS Last_Run_Date
, ord.run_status
, ord.run_duration
, A.date_modified
FROM dbo.sysjobs A
LEFT JOIN OrderedRunDates ord ON A.job_id = ord.job_id
WHERE ISNULL(ord.rn,1) = 1;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 10, 2016 at 10:02 am
Phil Parkin (8/10/2016)
TheFault (8/10/2016)
I've created the below query to retrieve some information on agent jobs scheduled which works fine and returns 33 rows. I'm trying to pull the next_scheduled_run_date from sysjobactivity, but whatever I try with JOINs on the 3rd table it returns more than 200 rows now (it's pulling back all rows where the job has ran rather than just the latest run). I just need the same data as original but with the next scheduled run date included also. It's probably really straightforward but I can't see the wood for the trees at the moment.Use MSDB
Go
Select Name, [Enabled], run_date as [Last_Run_Date], Run_Status, Run_Duration, Date_Modified
FROM sysjobs A
OUTER APPLY (SELECT TOP 1 * FROM sysjobhistory B
WHERE A.job_id = B.job_id
ORDER BY B.run_date DESC) as B
Thanks in advance
Seems to work OK for me, though I would suggest trying out the alternative method I show below too, for performance reasons:
USE msdb;GO
SELECT A.name
, A.enabled
, B.run_date AS Last_Run_Date
, B.run_status
, B.run_duration
, A.date_modified
FROM dbo.sysjobs A
OUTER APPLY (SELECT TOP 1
B.run_date
, B.run_status
, B.run_duration
FROM dbo.sysjobhistory B
WHERE A.job_id = B.job_id
ORDER BY B.run_date DESC
) AS B;
WITH OrderedRunDates
AS (SELECT jh1.job_id
, rn = ROW_NUMBER() OVER (PARTITION BY jh1.job_id ORDER BY jh1.run_date DESC)
, jh1.run_date
, jh1.run_status
, jh1.run_duration
FROM dbo.sysjobhistory jh1
)
SELECT A.name
, A.enabled
, ord.run_date AS Last_Run_Date
, ord.run_status
, ord.run_duration
, A.date_modified
FROM dbo.sysjobs A
LEFT JOIN OrderedRunDates ord ON A.job_id = ord.job_id
WHERE ISNULL(ord.rn,1) = 1;
It's not always faster Phil, but this slight variant is:
WITH OrderedRunDates
AS (SELECT jh1.job_id
, rn = ROW_NUMBER() OVER (PARTITION BY jh1.job_id ORDER BY jh1.run_date DESC)
, jh1.run_date
, jh1.run_status
, jh1.run_duration
FROM dbo.sysjobhistory jh1
)
SELECT A.name
, A.enabled
, ord.run_date AS Last_Run_Date
, ord.run_status
, ord.run_duration
, A.date_modified
FROM dbo.sysjobs A
LEFT JOIN OrderedRunDates ord ON A.job_id = ord.job_id AND ord.rn = 1;
--WHERE ISNULL(ord.rn,1) = 1;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 10, 2016 at 10:02 am
Here's an option. You can follow Phil's advice for a possible improvement on performance.
Use MSDB
Go
Select j.name,
j.[enabled],
B.run_date as [Last_Run_Date],
b.run_status,
b.run_duration,
j.date_modified,
B.next_scheduled_run_date
FROM sysjobs j
OUTER APPLY (SELECT TOP 1 h.*, a.next_scheduled_run_date
FROM sysjobhistory h
JOIN sysjobactivity a ON h.instance_id = a.job_history_id
WHERE j.job_id = h.job_id
ORDER BY h.run_date DESC) as B
ORDER BY Name;
August 10, 2016 at 10:20 am
ChrisM@Work (8/10/2016)
It's not always faster Phil, but this slight variant is:
Nice tweak, Chris. I often forget this nuance.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 10, 2016 at 10:43 am
I don't see any reason to conflate the last run with the next run. Also, when you query job activity, you need to include the session_id to make sure you don't see obsolete data:
SELECT
j.Name AS Job_Name, j.[Enabled],
jh.run_date as [Last_Run_Date], jh.Run_Status, jh.Run_Duration,
j.Date_Modified, ja.next_scheduled_run_date
FROM msdb.dbo.sysjobs j
LEFT OUTER JOIN (
SELECT jh2.*, ROW_NUMBER() OVER(PARTITION BY jh2.job_id ORDER BY run_date DESC) AS row_num
FROM msdb.dbo.sysjobhistory jh2
) AS jh ON jh.job_id = j.job_id AND jh.row_num = 1
LEFT OUTER JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = j.job_id AND
ja.session_id = (SELECT MAX(s.session_id) FROM msdb.dbo.syssessions s)
/*ORDER BY Job_Name*/
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 11, 2016 at 3:19 am
Thanks all, I'm tested out these solutions further as they are all bringing back slightly different run durations and run statuses each.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply