January 25, 2010 at 9:21 am
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!
January 25, 2010 at 10:46 am
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...
January 25, 2010 at 10:59 am
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
https://www.sqlserverblogforum.com/
January 25, 2010 at 11:03 am
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..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
January 25, 2010 at 3:20 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply