locked
SCRIPT for specific job history details RRS feed

  • Question

  • Hi All,

    Good Day!!

    i am looking for to pull the specific jb one week history with complete details and please share the script if have..

    Thanks,

    Ram


    RAM

    Wednesday, March 25, 2020 7:04 PM

Answers

  • Hi Ram,

    select 
     j.name as 'JobName',
     run_date,
     run_time,
     msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
     run_duration,
     avg(run_duration)over() as avg_run_duration
    From msdb.dbo.sysjobs j 
    INNER JOIN msdb.dbo.sysjobhistory h 
     ON j.job_id = h.job_id 
    where j.enabled = 1  --Only Enabled Jobs
    and cast(cast(run_date as varchar(10)) as date) >= DATEADD(day,-7, GETDATE()) --Last week to today
    order by JobName, RunDateTime desc

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Lily Lii Friday, March 27, 2020 6:31 AM
    • Marked as answer by TSRam Friday, March 27, 2020 4:47 PM
    Thursday, March 26, 2020 6:21 AM

All replies

  • Do a find on CHANGE THIS to add a WHERE clause for jobs, dates, etc.

    use msdb
    SELECT      [JobName]   = JOB.name,
                [Step]      = HIST.step_id,
                [StepName]  = HIST.step_name,
                [Message]   = HIST.message,
                [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'
                WHEN HIST.run_status = 1 THEN 'Succeeded'
                WHEN HIST.run_status = 2 THEN 'Retry'
                WHEN HIST.run_status = 3 THEN 'Canceled'
                END,
                --[RunDate]   = HIST.run_date,
                --[RunTime]   = HIST.run_time,
    			-- run_date and run_time as a datetime
    			msdb.dbo.agent_datetime(run_date,run_time) as RunDateTime,
    			-- run_duration as a StopDateTime
    			DATEADD(HOUR, CAST(Left(RIGHT('000000' + CONVERT(VARCHAR(6),run_duration),6 ), 2) as INT), 
    				DATEADD(MINUTE, CAST(SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),run_duration),6 ), 3,2) as INT), 
    				DATEADD(SECOND, CAST(right(RIGHT('000000' + CONVERT(VARCHAR(6),run_duration),6 ), 2) as INT), 
    				msdb.dbo.agent_datetime(run_date,run_time)))) as StopDateTime,
    			-- run_duration as a hh:mm:ss
    			STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6),run_duration),6 ),3,0,':'),6,0,':') AS "Duration_hh:mm:ss",
                --[Duration]  = HIST.run_duration,
                [Retries] = HIST.retries_attempted
    FROM        sysjobs JOB
    INNER JOIN  sysjobhistory HIST ON HIST.job_id = JOB.job_id
    -- CHANGE THIS 
    -- WHERE    JOB.name like '%GroupMaster%' or Job.name like '%etlv%'
    ORDER BY    msdb.dbo.agent_datetime(run_date,run_time) 

    Wednesday, March 25, 2020 7:22 PM
  • Thanks for quick response bro.. how to add average job duration time and next execution details as well 

    RAM

    Wednesday, March 25, 2020 7:37 PM
  • Take a look at this blog post

    http://bradsruminations.blogspot.com/2011/04/documenting-your-sql-agent-jobs.html


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Lily Lii Friday, March 27, 2020 6:31 AM
    Wednesday, March 25, 2020 9:34 PM
  • Hi Ram,

    select 
     j.name as 'JobName',
     run_date,
     run_time,
     msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
     run_duration,
     avg(run_duration)over() as avg_run_duration
    From msdb.dbo.sysjobs j 
    INNER JOIN msdb.dbo.sysjobhistory h 
     ON j.job_id = h.job_id 
    where j.enabled = 1  --Only Enabled Jobs
    and cast(cast(run_date as varchar(10)) as date) >= DATEADD(day,-7, GETDATE()) --Last week to today
    order by JobName, RunDateTime desc

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Lily Lii Friday, March 27, 2020 6:31 AM
    • Marked as answer by TSRam Friday, March 27, 2020 4:47 PM
    Thursday, March 26, 2020 6:21 AM
  • Hi Ram,

    Do the answers above help you? It's so kind of you to mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, March 27, 2020 6:31 AM