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
August 12, 2011 at 10:39 am
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.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply