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

How to get the Job Status in Query Expand / Collapse
Author
Message
Posted Monday, January 25, 2010 9:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 18, 2014 5:44 AM
Points: 84, Visits: 394
Hi,

Can anyone please help me the query to get the Status of Jobs in a server.. i.e for ex Job A - Executing ,Job B- Failed ,Job C - Success etc.,

Thanks in Advance!
Post #853083
Posted Monday, January 25, 2010 10:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:52 AM
Points: 5, Visits: 94
To get the Job status information, we need to use the relevant information from two job related tables namely sysjobs and sysjobhistory.
The run_status column of sysjobhistory has the current status of the job.
You can refer to the following script:

select distinct j.Name as "Job Name",h.run_date as LastStatusDate,
case h.run_status
when 0 then 'Failed'
when 1 then 'Successful'
when 3 then 'Cancelled'
when 4 then 'Executing'
end as JobStatus
from msdb.dbo.sysJobHistory h, msdb.dbo.sysJobs j
where j.job_id = h.job_id and h.run_date =
(select max(hi.run_date) from msdb.dbo.sysJobHistory hi where h.job_id = hi.job_id)

Hope this will help you...
Post #853173
Posted Monday, January 25, 2010 10:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 1,157, Visits: 4,650
haichells (1/25/2010)
Hi,

Can anyone please help me the query to get the Status of Jobs in a server.. i.e for ex Job A - Executing ,Job B- Failed ,Job C - Success etc.,

Thanks in Advance!


Right click job name-->view history...


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #853177
Posted Monday, January 25, 2010 11:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,646, Visits: 1,947
muthukkumaran (1/25/2010)

Right click job name-->view history...


The OP wants it deliberately from the query... I guess the OP might be aware of this..



Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
Post #853179
Posted Monday, January 25, 2010 3:20 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:23 AM
Points: 414, Visits: 2,897
This will give you the current status of jobs running, saw it on a forum site I use it to monitor long running jobs, they are still running so no history

execute master.dbo.xp_sqlagent_enum_jobs 1,
'garbage' -- doesn't seem to matter what you put here


Andrew



Post #853345
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse