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 ««12

T-SQL statement to return SQL Job Last Run Date Expand / Collapse
Author
Message
Posted Monday, January 12, 2009 8:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 12, 2009 9:39 AM
Points: 7, Visits: 11
Thanks for posting this script. I'm having trouble modifying it to my use though. I'm trying to create a script that I can have dbmail run to email me the last run status of every job. The script given, if I remove the where section works, except it gives the last run for each type of status. If there's been a failure and a success within it's history it shows both with dates.

Any ideas on how I can just have it show the late run status and time?
Post #634752
Posted Monday, January 12, 2009 9:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 AM
Points: 7,056, Visits: 7,280
SELECT j.[name], 
CAST(STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime) AS [LastRun],
CASE jh.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS [Status]
FROM (SELECT a.job_id,MAX(a.instance_id) As [instance_id]
FROM msdb.dbo.sysjobhistory a
WHERE a.step_id = 0
GROUP BY a.job_id) b
INNER JOIN msdb.dbo.sysjobhistory jh ON jh.instance_id=b.instance_id
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id




Far away is close at hand in the images of elsewhere.

Anon.

Post #634790
Posted Monday, January 12, 2009 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 12, 2009 9:39 AM
Points: 7, Visits: 11
Thank you. That's exactly what I was looking for.
Post #634799
Posted Monday, June 8, 2009 7:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:01 AM
Points: 34, Visits: 674
Thank you for the script Wildcat, it's very useful for me!
Post #730663
Posted Wednesday, September 1, 2010 5:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:19 AM
Points: 321, Visits: 681
SELECT j.[name],
MAX(CAST(
STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRun],
MAX(CAST(
STUFF(STUFF(CAST(sjs.next_run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(sjs.next_run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [NextRun],
CASE jh.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh
ON jh.job_id = j.job_id AND jh.step_id = 0
inner join msdb.dbo.syscategories sc
on j.category_id = sc.category_id
INNER JOIN MSDB.dbo.sysjobschedules sjs
on j.job_id = sjs.Job_id
GROUP BY j.[name], jh.run_status
Post #978655
Posted Friday, August 12, 2011 10:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
Word of warning : if the history has been purged/deleted then sysjobhistory will not display the same data as "Job Activity Monitor" i.e. "Job Activity Monitor" will display a last run date but this is not obtainable from running queries against sysjobhistory where the history has been purged because it just won't exist.

Use sysjobactivity to obtain the "real" last run date and to be in agreement with "Job Activity Monitor".

Try it; create a test job, run it a few times, manually delete the latest history records then run your query using sysjobhistory. You will find that your queries report an earlier last run time than SQL's "Job Activity Monitor".

Does it matter ? Well it may do, I noticed this problem when deciding which jobs could be deleted. It seemed that some jobs were never run but this was not the case as the history had been deleted.
Post #1159388
Posted Friday, February 1, 2013 4:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 1, 2013 6:32 PM
Points: 20, Visits: 157
Do you have the script please to use sysjobactivity?
I would like to bring all the jobs with their names, steps, and the status, last run time, even if there is no history, or the jobs is disabled, not scheduled
Post #1414871
Posted Wednesday, June 18, 2014 9:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 7:55 PM
Points: 1, Visits: 62
Bringing a thread back from the dead but to help people that stumble accross this page in the future

you could use the sql system function msdb.dbo.agent_datetime(run_date, run_time) to calculate the date time value from the run_date and run_time integer values stored in the msdb.dbo.sysjobhistory table.

e.g.

Select J.name,MAX(msdb.dbo.agent_datetime(run_date, run_time)) as 'LastLogRun'
FROM msdb.dbo.sysjobhistory H
Inner join msdb.dbo.sysjobs J ON H.job_id = J.job_id
where step_id = 0
GROUP BY J.name
Post #1583535
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse