Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL statement to return SQL Job Last Run Date


T-SQL statement to return SQL Job Last Run Date

Author
Message
Prasad Bhogadi
Prasad Bhogadi
SSC Eights!
SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)

Group: General Forum Members
Points: 967 Visits: 450
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
David Burrows
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7974 Visits: 9442
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.


Prasad Bhogadi
Prasad Bhogadi
SSC Eights!
SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)

Group: General Forum Members
Points: 967 Visits: 450
Thank you David.

Prasad Bhogadi
www.inforaise.com
azeem.anchamparuthi
azeem.anchamparuthi
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 9
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
Wildcat
Wildcat
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 1444
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
azeem.anchamparuthi
azeem.anchamparuthi
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 9
Thank you very much for the script.
Coop-546061
Coop-546061
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 23
Do you know how to get the last datetime of SQL replication? Thank you!
Wildcat
Wildcat
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 1444
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.
Coop-546061
Coop-546061
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 23
Can you please give me an example SQL Statement to use to get the last datetime of the scheduled replication? Thanks
raoul.teeuwen
raoul.teeuwen
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 27
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search