T-SQL statement to return SQL Job Last Run Date

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » T-SQL statement to return SQL Job Last Run...

17 posts, Page 1 of 2

1 2 »»

T-SQL statement to return SQL Job Last Run Date

Rate Topic

Display Mode

Topic Options

Author

Message

Prasad Bhogadi

Posted Monday, October 15, 2007 6:28 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 02, 2013 5:00 AM
Points: 967, Visits: 447

Hi,

Is there anyway that we can retrieve the Last Run date of a SQL Job using T-SQL.

Thanks

Prasad Bhogadi

Post #410789

David Burrows

Posted Monday, October 15, 2007 6:52 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 6,380, Visits: 5,432

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

Prasad Bhogadi

Posted Monday, October 15, 2007 6:57 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 02, 2013 5:00 AM
Points: 967, Visits: 447

Thank you David.

Prasad Bhogadi

Post #410802

azeem.anchamparuthi

Posted Thursday, June 12, 2008 11:32 AM

Forum 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

Wildcat

Posted Thursday, June 12, 2008 1:53 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 10:10 PM
Points: 307, Visits: 1,390

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

azeem.anchamparuthi

Posted Monday, June 16, 2008 1:04 PM

Forum 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

Coop-546061

Posted Tuesday, October 28, 2008 8:27 PM

Valued 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

Wildcat

Posted Thursday, October 30, 2008 12:35 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 10:10 PM
Points: 307, Visits: 1,390

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

Coop-546061

Posted Thursday, October 30, 2008 4:19 PM

Valued 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

raoul.teeuwen

Posted Friday, November 28, 2008 4:36 AM

Forum 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 »

17 posts, Page 1 of 2

1 2 »»

Permissions