Help on a Query on sysjobshistory
-
Hello all, Im working on a query to make db admin easier for me (ignore all the substrings!)
select distinct j.Name collate Latin1_General_CI_AS as 'Job Name',
'Internal Live Server' as 'Server Name',
CONVERT(VARCHAR(25),CAST((
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),1,4)
+ '/' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),5,2)
+ '/' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),7,2)
+ ' ' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),9,2)
+ ':' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),11,2)
+ ':' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),13,2)
) as datetime)) as 'Last Run Date',
case h.run_status
when 0 then 'Failed'
when 1 then 'Successful'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as 'Job Status'
from msdb.dbo.sysJobHistory h, msdb.dbo.sysJobs j
where j.job_id = h.job_id and h.run_date =
(select max(hi.run_date) from msdb.dbo.sysJobHistory hi where h.job_id = hi.job_id)
This returns an output of all jobs that have run and their last run status as follows
DB Backup Job for DB Maintenance Plan 'All User DB' Internal Live Server Dec 7 2006 7:45PM Successful
DB Backup Job for DB Maintenance Plan 'System DB' Internal Live Server Dec 7 2006 7:00PM Successful
Optimizations Job for DB Maintenance Plan 'All User DB' Internal Live Server Dec 7 2006 7:15PM Successful
Optimizations Job for DB Maintenance Plan 'System DB' Internal Live Server Dec 3 2006 1:00AM Successful
ServerSpaceCheck Internal Live Server Dec 8 2006 1:34PM Successful
ServerSpaceCheck Internal Live Server Dec 8 2006 2:00PM Successful
Transaction Log Backup Job for DB Maintenance Plan 'Transaction Log Backup' Internal Live Server Dec 8 2006 1:00PM Successful
Transaction Log Backup Job for DB Maintenance Plan 'Transaction Log Backup' Internal Live Server Dec 8 2006 2:00PM Successful
Only problem is if a job is run more than once in a day such as the ServerSpaceCheck and Trans Log backups it returns all these jobs for the day as the query is filtering on max(run_date). I need it to filter on max(run_date) and the max(run_time) in order to get the last job run time but i am having great dificulty! by the way i hate the sysjobshistory table design hence all the substrings above to get the Date and time put together in a user friendly way!
The original query is actually referencing a linked server using the server.database.owner.object format but I have stripped this in order to make the it easier to visualise. you can simply copy and paste the above script into any server.
Please HELP!
Sizla
-
Sorry for the lack of formatting but I guess you can handle that at this point.
PS. I would probabely add the job category to this report as well :
SELECT J.name COLLATE Latin1_General_CI_AS AS 'Job Name', 'Internal Live Server' AS 'Server Name', CONVERT(VARCHAR(25),
CAST(SUBSTRING(CONVERT(varchar(10), Main.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(Main.run_time AS varchar(6))))
+ CAST(Main.run_time AS varchar(6)), 1, 4) + '/' + SUBSTRING(CONVERT(varchar(10), Main.run_date) + REPLICATE('0',
6 - DATALENGTH(CAST(Main.run_time AS varchar(6)))) + CAST(Main.run_time AS varchar(6)), 5, 2) + '/' + SUBSTRING(CONVERT(varchar(10),
Main.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(Main.run_time AS varchar(6)))) + CAST(Main.run_time AS varchar(6)), 7, 2)
+ ' ' + SUBSTRING(CONVERT(varchar(10), Main.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(Main.run_time AS varchar(6))))
+ CAST(Main.run_time AS varchar(6)), 9, 2) + ':' + SUBSTRING(CONVERT(varchar(10), Main.run_date) + REPLICATE('0',
6 - DATALENGTH(CAST(Main.run_time AS varchar(6)))) + CAST(Main.run_time AS varchar(6)), 11, 2) + ':' + SUBSTRING(CONVERT(varchar(10),
Main.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(Main.run_time AS varchar(6)))) + CAST(Main.run_time AS varchar(6)), 13, 2) AS datetime))
AS 'Last Run Date',
CASE Main.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Successful' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'In Progress' END AS 'Job Status'
FROM msdb.dbo.sysjobhistory Main INNER JOIN
msdb.dbo.sysjobs J ON Main.job_id = J.job_id INNER JOIN
(SELECT dtDates.job_id, dtDates.mrun_date, MAX(JH.run_time) AS Mrun_time
FROM msdb.dbo.SysJobHistory JH INNER JOIN
(SELECT job_id, MAX(run_date) AS mrun_date
FROM msdb.dbo.SysJobHistory
GROUP BY job_id) dtDates ON JH.job_id = dtDates.job_id AND JH.run_date = dtDates.mrun_date
GROUP BY dtDates.job_id, dtDates.mrun_date) dtDTime ON Main.job_id = dtDTime.job_id AND Main.run_date = dtDTime.mrun_date AND
Main.run_time = dtDTime.Mrun_time
-
I misread my results. Looks like you need to add a distinct to this query. More than 1 job step can have the same run_time.
Maybe you could add a condition to check only for steps 0 only. But then again the job can be manually started on any step so I'm not sure it would be advisable (thinking out loud, did not test this idea). Not to mention that step 0 may not be linked to the max run_time.
-
well its given me a new angle...thanks for that. Im getting individual jobs returned but they are duplicates now. I will investigate!
Superfast respons as always!
-
Quoting myself
.I misread my results. Looks like you need to add a distinct to this query because more than 1 job step can have the same run_time.
-
Use your column 'Last Run Date' to determine the last job run. Its already a valid date.
select distinct j.Name collate Latin1_General_CI_AS as 'Job Name',
server as 'Server Name',
Max(CAST((
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),1,4)
+ '/' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),5,2)
+ '/' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),7,2)
+ ' ' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),9,2)
+ ':' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),11,2)
+ ':' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),13,2)
) as datetime)) as 'Last Run Date',
case h.run_status
when 0 then 'Failed'
when 1 then 'Successful'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as 'Job Status'
from msdb.dbo.sysJobHistory h, msdb.dbo.sysJobs j
where j.job_id = h.job_id
group by
j.Name collate Latin1_General_CI_AS, server, h.run_status
-- the server column is optional.
-
There is also a need to know that max time of the run (in the last day). And you can't use max directly in the last query because all those rows are distinct. So what I did :
Get the max date per job
self join and get max date for that day
self join again and fetch all required columns.
-
Please note that 'Last Run Date' contains both the date and the time the job was run. Running MAX on it would consider both the date and time.
-
And that's why we usually ask for sample data. Now let me go buy some new eyes and I'll be right back. Thanx for letting us know
. -
Hello gentleman, thank you both for the input which proved very helpful and gave me a few angles to work from.
In the end I decided to go for Ninja's initial suggestion with the addtion of a distinct clause as suggested. This seemed to do the trick and I now have a working solution and a nice automated email report in the morning.
Thanks, Siz
-
I'm normally a performance freak... but since this is a daily report, I guess it won't kill the server to have a suboptimal query
.If anyone ever needs to rebuild this query, I'd strongly suggest using a single derived table instead of 2 like I just did!
-
I was quite worried bout that too. But surprisingly I'm running the script across four linked servers as a distributed query and UNION ALL for the results and it takes 1 second.
I'm sure this can be speeded up as you say. Im quite new to TSQL develpment(learning evry day!). An example of how you would implement using a single derived table would be very appreciated
SELECT DISTINCT Live Server' AS 'Server Name',
J.name COLLATE Latin1_General_CI_AS AS 'Job Name',
CONVERT(VARCHAR(25), CAST(
SUBSTRING(CONVERT(varchar(10), Main.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(Main.run_time AS varchar(6)))) + CAST(Main.run_time AS varchar(6)), 1, 4)
+ '/' + SUBSTRING(CONVERT(varchar(10), Main.run_date) + REPLICATE('0',6 - DATALENGTH(CAST(Main.run_time AS varchar(6)))) + CAST(Main.run_time AS varchar(6)), 5, 2)
+ '/' + SUBSTRING(CONVERT(varchar(10), Main.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(Main.run_time AS varchar(6)))) + CAST(Main.run_time AS varchar(6)), 7, 2)
+ ' ' + SUBSTRING(CONVERT(varchar(10), Main.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(Main.run_time AS varchar(6)))) + CAST(Main.run_time AS varchar(6)), 9, 2)
+ ':' + SUBSTRING(CONVERT(varchar(10), Main.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(Main.run_time AS varchar(6)))) + CAST(Main.run_time AS varchar(6)), 11, 2)
+ ':' + SUBSTRING(CONVERT(varchar(10), Main.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(Main.run_time AS varchar(6)))) + CAST(Main.run_time AS varchar(6)), 13, 2)
AS datetime)) AS 'Last Run Date',
CASE Main.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Successful'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS 'Job Status'
FROM msdb.dbo.sysjobhistory Main
INNER JOIN
msdb.dbo.sysjobs J ON Main.job_id = J.job_id
INNER JOIN
(SELECT dtDates.job_id, dtDates.mrun_date, MAX(JH.run_time) AS Mrun_time
FROM msdb.dbo.SysJobHistory JH
INNER JOIN
(SELECT job_id, MAX(run_date) AS mrun_date
FROM msdb.dbo.SysJobHistory GROUP BY job_id) dtDates ON JH.job_id = dtDates.job_id AND JH.run_date = dtDates.mrun_date
GROUP BY dtDates.job_id, dtDates.mrun_date) dtDTime ON Main.job_id = dtDTime.job_id
AND Main.run_date = dtDTime.mrun_date AND Main.run_time = dtDTime.Mrun_time
-
I would tell you to use Ronald's version but it takes 5 times more time to run than my version on my system. I'll let you check out which version is fastest in your environement but I'll stick to my version for now (no time to figure the difference out ATM).
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply