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;
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.
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy