How to get the Job Status in Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » How to get the Job Status in Query

How to get the Job Status in Query

Rate Topic

Display Mode

Topic Options

Author

Message

haichells

Posted Monday, January 25, 2010 9:21 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 14, 2012 6:42 AM
Points: 78, Visits: 375

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

Kumari Punam

Posted Monday, January 25, 2010 10:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:32 AM
Points: 5, Visits: 89

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

muthukkumaran Kaliyamoorthy

Posted Monday, January 25, 2010 10:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:22 AM
Points: 1,123, Visits: 4,423

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

Bru Medishetty

Posted Monday, January 25, 2010 11:03 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,735, 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

andrewkane17

Posted Monday, January 25, 2010 3:20 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:09 PM
Points: 414, Visits: 2,698

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 »

Permissions