T-SQL statement to return SQL Job Last Run Date

  • Prasad Bhogadi

    SSCrazy Eights

    Points: 9235

    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

    SSC Guru

    Points: 64396

    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

    SSCrazy Eights

    Points: 9235

    Thank you David.

    Prasad Bhogadi
    www.inforaise.com

  • azeem.anchamparuthi

    SSC Veteran

    Points: 234

    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

    SSCarpal Tunnel

    Points: 4714

    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

    SSC Veteran

    Points: 234

    Thank you very much for the script.

  • Coop-546061

    SSC Veteran

    Points: 294

    Do you know how to get the last datetime of SQL replication? Thank you!

  • Wildcat

    SSCarpal Tunnel

    Points: 4714

    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

    SSC Veteran

    Points: 294

    Can you please give me an example SQL Statement to use to get the last datetime of the scheduled replication? Thanks

  • raoul.teeuwen

    SSC Enthusiast

    Points: 198

    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.

  • James B-487162

    Valued Member

    Points: 71

    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?

  • David Burrows

    SSC Guru

    Points: 64396

    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.

  • James B-487162

    Valued Member

    Points: 71

    Thank you. That's exactly what I was looking for.

  • lucazav

    SSC Eights!

    Points: 871

    Thank you for the script Wildcat, it's very useful for me!

  • rxm119528

    SSCrazy

    Points: 2900

    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 19 total)

You must be logged in to reply to this topic. Login to reply