I know I can view a job's status from Enterprise Manager, but I need to view the status from a program or SQL query. How can I create an SQL batch to programmatically test whether SQL Server Agent is running a job?

Say you have a job on your server called InfiniteLoop and you want to see if the job is running. By using SQL Server Profiler to watch the T-SQL code that Enterprise Manager sends to SQL Server, you can see that Enterprise Manager retrieves job-status information by running the sp_help_job stored procedure:

EXEC msdb..sp_help_job
@job_name = 'InfiniteLoop'
,@job_aspect = N'job'

The result set includes a column called current_execution_status, but you might not know how to interpret the column values because SQL Server Books Online (BOL) doesn't document them. However, you can learn a lot by directly reading stored-procedure code. The parameter-declaration section of sp_help_job, as shown below, tells you how to interpret the value of current_execution_status:

@execution_status INT = NULL,
  — 1 = Executing,
  — 2 = Waiting For Thread,
  — 3 = Between Retries,
  — 4 = Idle,
  — 5 = Suspended,
  — 6 = [obsolete],
  — 7 = PerformingCompletion
  — Actions

For example, a job that's running will have a value of 1; values 2 and 3 show that the job has started but isn't executing commands. Now, you can pass the result set to your program by using the syntax INSERT INTO MyTable EXEC MyProc, which stores sp_help_job's output in a table.

By using Profiler to trace Enterprise Manager's operations, you can find out how to do many things that BOL doesn't document. And reading code in system stored procedures can teach you a lot about SQL Server and how to get information out of it.

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

I tried this and got this Error

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67 An INSERT EXEC statement cannot be nested.

(0 row(s) affected)

create table test_job ( job_id UNIQUEIDENTIFIER null , job_name sysname null, job_aspect VARCHAR(9) null , -- Job set parameters job_type VARCHAR(12) null , owner_login_name sysname null , subsystem NVARCHAR(40) null , category_name sysname null , enabled TINYINT null , execution_status INT null , date_comparator CHAR(1) null , date_created DATETIME null , date_last_modified DATETIME null, description NVARCHAR(512) null )

go insert into test_job exec msdb..sp_help_job

alainl- July 19, 2004

Article Rating 3 out of 5

I get the same error. It would be nice if the author had posted some sample code.

centexhomes- February 09, 2005

Article Rating 1 out of 5

This seems to work for me in SQL 2000. Sorry, comments removed so I could fit it in one reply. -Decayschampion

create procedure sp_jobstate (@job_id UNIQUEIDENTIFIER) as begin if not exists (select * from msdb..sysjobs where job_id=@job_id) begin select 'Invalid job id specified' return(0) end set nocount on declare @is_sysadmin int declare @job_owner sysname declare @running int declare @status int

CREATE TABLE #xp_results ( job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL) SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0),@job_owner = SUSER_SNAME() INSERT INTO #xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id select 'Job: '+sj.[name]+ case xp.running when 0 then ' is not running' when 1 then ' is running' else ' is in an unexpected state' end, char(13)+'Status: '+ case xp.job_state when 1 then 'Executing' when 2 then 'Waiting For Thread' when 3 then 'Between Retries' when 4 then 'Idle' when 5 then 'Suspended' when 6 then '[obsolete]' when 7 then 'PerformingCompletion' end from msdb..sysjobs sj join #xp_results xp on (sj.job_id=xp.job_id) drop table #xp_results end

Anonymous User- August 15, 2005

Article Rating 2 out of 5

Whoops, looks like it got shredded. Anyone who wants a "clean" copy can email me at dan_wunder@[removethispart]hotmail.com

Anonymous User- August 15, 2005

Article Rating 2 out of 5

 
 

ADS BY GOOGLE