October 15, 2007 at 6:28 am
Hi,
Is there anyway that we can retrieve the Last Run date of a SQL Job using T-SQL.
Thanks
Prasad Bhogadi
www.inforaise.com
October 15, 2007 at 6:52 am
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]
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh
ON jh.job_id = j.job_id AND jh.step_id = 0
WHERE j.[name] = 'jobname'
GROUP BY j.[name]
Far away is close at hand in the images of elsewhere.
Anon.
October 15, 2007 at 6:57 am
Thank you David.
Prasad Bhogadi
www.inforaise.com
June 12, 2008 at 11:32 am
David, thank you for the post. I ran the query and it returned the date of last DTS run, do you know how to get the last "successfull" DTS run
Thanks,
A
June 12, 2008 at 1:53 pm
Based on David's script:
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],
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
WHERE sc.[name] like '%DTS%'
GROUP BY j.[name], jh.run_status
June 16, 2008 at 1:04 pm
Thank you very much for the script.
October 28, 2008 at 8:27 pm
Do you know how to get the last datetime of SQL replication? Thank you!
October 30, 2008 at 12:35 pm
Coop (10/28/2008)
Do you know how to get the last datetime of SQL replication? Thank you!
Not sure what you asked.
There are several _history tables in distribution db, like, MSdistribution_history, MSlogreader_history, etc... You can get some info from there.
October 30, 2008 at 4:19 pm
Can you please give me an example SQL Statement to use to get the last datetime of the scheduled replication? Thanks
November 28, 2008 at 4:36 am
for a lot of examples on how to query dts schedule fields see http://raoulteeuwen.blogspot.com/2008/09/query-job-last-run-status-in-ms-sql.html and the link in that blog to http://doc.ddart.net/mssql/sql70/sp_help_27.htm ... it shows you how you can check last run date, last run status etc.
January 12, 2009 at 8:54 am
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?
January 12, 2009 at 9:35 am
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.
January 12, 2009 at 9:44 am
Thank you. That's exactly what I was looking for.
June 8, 2009 at 7:19 am
Thank you for the script Wildcat, it's very useful for me!
September 1, 2010 at 5:17 am
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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply