Identifying running SQL Agent jobs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Identifying running SQL Agent jobs

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-21 : 08:26:50

Gary writes "Using SQL Server 7, Service Pack 3 on a Windows NT 4 Build 1381 Service Pack 6 box

I need to identify what SQL Agent jobs are currently running.

I put together the following view, but it did not work.

CREATE VIEW What_jobs_are_running
AS
SELECT top 30 name,
case b.run_status
when 0 then 'failed'
when 1 then 'succeeded'
when 2 then 'retry'
when 3 then 'canceled'
when 4 then 'in progress'
end
as 'run status',
b.sql_severity as 'err severity',
b.run_date as 'run date',
b.step_name, b.server,
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 that above view be made to work. I just want to know how to detect what SQL Agent jobs are running at any given time?

TIA Gary"

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-06-21 : 09:56:20

EXEC msdb..sp_help_job

Look at the value of the current_execution_status
Here's the key for the values:
1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 = PerformingCompletionActions

solart
Posting Yak Master

148 Posts

Posted - 2002-06-23 : 12:43:20

Thanks, works great!!

solart
Posting Yak Master

148 Posts

Posted - 2002-06-23 : 12:43:28

Thanks, works great!!

solart
Posting Yak Master

148 Posts

Posted - 2002-06-23 : 12:43:33

Thanks, works great!!

solart
Posting Yak Master

148 Posts

Posted - 2002-06-23 : 12:43:54

Thanks, works great!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-23 : 13:27:05

So, uh, how well does it work?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-08-11 : 06:22:43

How about for stop state or status due to failure to execute the job?

quote:
Originally posted by YellowBug

EXEC msdb..sp_help_job

Look at the value of the current_execution_status
Here's the key for the values:
1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 = PerformingCompletionActions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources