October 7, 2007 at 3:54 pm
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"
October 19, 2008 at 11:30 am
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.
November 4, 2008 at 10:29 am
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
November 4, 2008 at 10:35 am
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
January 7, 2009 at 5:57 pm
I found this article useful. Thanks!
June 26, 2009 at 4:11 am
January 25, 2012 at 2:52 pm
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]
February 20, 2015 at 6:04 am
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