How do I determine SQL Agent jobs active?
Posted on 2002-06-19
SQL 7 Agent runs enabled jobs, at their appropriate times (or on request). At any moment in time from zero thru N jobs could currently be active. I would like to be able to run a query which would provide me with the information on what jobs are currently running when I submit the query. Following is a view I tried creating to give me the info, but it does not appear to work. I can create a SQL Agent Job which simply does a "waitfor" for 60 seconds, start it manually, then run the below. The running/active job is not detected by my query.
CREATE VIEW What_jobs_are_running
SELECT top 30 name,
when 0 then 'failed'
when 1 then 'succeeded'
when 2 then 'retry'
when 3 then 'canceled'
when 4 then 'in progress'
as 'run status',
b.sql_severity as 'err severity',
b.run_date as 'run date',
b.message as 'error message'
FROM msdb..sysjobs a inner join sysjobhistory b on a.job_id = b.job_id
WHERE name like '%' and b.run_status = 4
order by b.run_date desc, a.name asc
I am not asking for the above code to be debugged.
I simply want to know how to query to find out what SQL Agent jobs are running/active.