Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

To Get Latest SQL Job Status Expand / Collapse
Author
Message
Posted Sunday, October 7, 2007 3:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 12:16 PM
Points: 148, Visits: 92
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"
Post #407802
Posted Sunday, October 19, 2008 11:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 19, 2014 12:02 AM
Points: 69, Visits: 228
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.
Post #588276
Posted Tuesday, November 4, 2008 10:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 4, 2008 10:27 AM
Points: 2, Visits: 6
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 :D :

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

Post #596740
Posted Tuesday, November 4, 2008 10:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 4, 2008 10:27 AM
Points: 2, Visits: 6
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

Post #596748
Posted Wednesday, January 7, 2009 5:57 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 10, 2012 6:08 PM
Points: 1,156, Visits: 801
I found this article useful. Thanks!
Post #632024
Posted Friday, June 26, 2009 4:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:28 AM
Points: 7, Visits: 233
You will never get "In Progress" status.
See: http://www.sqlservercentral.com/articles/Administration/howtofindthatjobisrunning/2071/

Lacc
Post #742506
Posted Wednesday, January 25, 2012 2:52 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 5:36 PM
Points: 1,339, Visits: 596
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!


--www.sqlvillage.com
Post #1241808
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse