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, October 15, 2007 6:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:47 AM
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
Post #410789
Posted Monday, October 15, 2007 6:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 9:30 AM
Points: 7,057, Visits: 7,287
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.

Post #410799
Posted Monday, October 15, 2007 6:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:47 AM
Points: 967, Visits: 450
Thank you David.

Prasad Bhogadi
www.inforaise.com
Post #410802
Posted Thursday, June 12, 2008 11:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2008 10:20 AM
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
Post #516124
Posted Thursday, June 12, 2008 1:53 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 7, 2014 12:18 PM
Points: 413, Visits: 1,438
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
Post #516240
Posted Monday, June 16, 2008 1:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2008 10:20 AM
Points: 2, Visits: 9
Thank you very much for the script.
Post #517819
Posted Tuesday, October 28, 2008 8:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 17, 2009 11:37 AM
Points: 68, Visits: 23
Do you know how to get the last datetime of SQL replication? Thank you!
Post #593336
Posted Thursday, October 30, 2008 12:35 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 7, 2014 12:18 PM
Points: 413, Visits: 1,438
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.
Post #594616
Posted Thursday, October 30, 2008 4:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 17, 2009 11:37 AM
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
Post #594706
Posted Friday, November 28, 2008 4:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 22, 2010 9:08 AM
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.
Post #610243
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse