To Get Latest SQL Job Status

  • Comments posted to this topic are about the item To Get Latest SQL Job Status

    Thanks
    Mohit Nayyar
    http://mohitnayyar.blogspot.com/
    "If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

  • Hi Mohit,

    Regarding the latest Job status query you have posted, i have tried that but it doesn't gave me the expected result.

    Here is what i have done , i have put the T-SQL in the job which will execute infinite times, so the Job Status is In Progress, but when i fired the query of the Job Status it still gives me status as successful.

    Is i am going wrong somewhere , here is the query which i have used so that the Job should remain in the In Progress state. When i stop the job it does show me the Canceled status but In Progress it was not showing.

    Code Snippet:

    declare @count int

    set @count=0

    while(@count<100)

    print 'hi'

    set @count=@count+0

    Looking forward for positive response.

  • I added last run time and duration, and FQDN for msdb table to allow this to be used in a stored procedure or elsewhere than Query Analyser 😀 :

    select distinct j.Name as "Job Name", j.description as "Job Description", h.run_date as LastStatusDate, h.run_time as LastStatusTime, h.run_duration as LastStatusDuration,

    case h.run_status

    when 0 then 'Failed'

    when 1 then 'Successful'

    when 3 then 'Cancelled'

    when 4 then 'In Progress'

    end as JobStatus

    from msdb..sysJobHistory h, msdb..sysJobs j

    where j.job_id = h.job_id

    and h.step_id = 1

    and h.run_date =

    (select max(hi.run_date) from msdb..sysJobHistory hi where h.job_id = hi.job_id)

    and h.run_time =

    (select max(hj.run_time) from msdb..sysJobHistory hj where h.job_id = hj.job_id)

    order by 1

  • And this, to include category information....

    select distinct cat.name as "Category", j.Name as "Job Name", j.description as "Job Description",

    h.run_date as LastStatusDate, h.run_time as LastStatusTime, h.run_duration as LastStatusDuration,

    case h.run_status

    when 0 then 'Failed'

    when 1 then 'Successful'

    when 3 then 'Cancelled'

    when 4 then 'In Progress'

    end as JobStatus

    from msdb..sysJobHistory h, msdb..sysJobs j, msdb..syscategories cat

    where j.job_id = h.job_id and

    j.category_id = cat.category_id

    and h.step_id = 1

    and h.run_date =

    (select max(hi.run_date) from msdb..sysJobHistory hi where h.job_id = hi.job_id)

    and h.run_time =

    (select max(hj.run_time) from msdb..sysJobHistory hj where h.job_id = hj.job_id)

    order by 1,3

  • I found this article useful. Thanks!

  • You will never get "In Progress" status.

    See: http://www.sqlservercentral.com/articles/Administration/howtofindthatjobisrunning/2071/[/url]

    Lacc

  • Hi Mohit

    Nice posting!

    One of my DBA brought your post in my attention when he was working on finding list of jobs current status is not successful.

    I happened to notice little change requirement in your query:

    You should replace last part using max(instance_id) that will help user to find latest status even when job ran multiple times in a day. To be precise:

    syntax part:

    instance_id = (select max(hi.instance_id) from msdb.dbo.sysjobhistory hi where h.job_id = hi.job_id)

    Just thought and suggestion!

    [font="Verdana"]--www.sqlvillage.com[/size][/font]

  • This query doesn't take into account the time the job ran - if there is more than one run in a day we have not specified how we want this to be dealt with.

    SQL Rank gives us a good idea of what we're looking at - also selecting step id = 0 gives the job outcome.

    Select

    [Job Name] = j.name

    , [Job Description] = j.description

    , [LastRunDate] = h.run_date

    , [LastRunTime] = h.run_time

    , [JobStatus] = Case h.run_status

    When 0 Then 'Failed'

    When 1 Then 'Successful'

    When 3 Then 'Cancelled'

    When 4 Then 'In Progress'

    End

    ,[OrderOfRun] = Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc)

    From

    msdb.dbo.sysjobhistory h

    Left join msdb.dbo.sysjobs j On j.job_id = h.job_id

    Where h.step_id=0 --only look @ Job Outcome step

    Order By [Job Name] desc,Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc) Asc

Viewing 8 posts - 1 through 7 (of 7 total)

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