Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool
Main
Detecting The State of a SQL Server Agent Job - How to Find SQL Server Job Execution Status - Using xp_sqlagent_enum_jobs - Retrieve Job status for running job

There is a stored procedures, that Microsoft provide, which give some insight into the status, and information, about a job - sp_help_job. This stored procedure procedure provides information such as last start time, job status, and such:

XP_SQLAGENT_ENUM_JOBS is an undocumented proc inside of sp_help_jobs an is used extensivly to get sql agent job info.

The sp_help_job stored procedure has a parameter named execution_status that can be used to return information for all jobs that have a particular status. For example, command to retrieve job information for all jobs that currently have a status of "Executing":
exec msdb.dbo.sp_help_job @execution_status = 1

The code behind the sp_help_job stored procedure, has extended stored procedure (XP) xp_sqlagent_enum_jobs. This XP can be used to return job information. The xp_sqlagent_enum_jobs extended stored procedure can be found in the master database.xp_sqlagent_enum_jobs extended stored procedure can be used to return information for all jobs, to return the job information for jobs own by a particular login, or the job information for a particular job identification number. xp_sqlagent_enum_jobs extended stored procedure inside of sp_help_jobs an is used extensivly to get sql agent job info

With this approach, you could use to identify the state of a SQL Server Agent job, or , for example, use xp_sqlagent_enum_jobs extended stored procedure to help identify long running job. Or, start and monitor a job to completion using T-SQL. Or, how to monitor a long running SQL job, and generate alerts based on that information.
 
	drop procedure sp_get_SQL_job_execution_status
	go

	create procedure sp_get_SQL_job_execution_status
	(
		@job_name sysname
		, @select_data int =0
		, @execution_status int =null output
	
	)
	as

	set nocount on


	/*
		Is the execution status for the jobs. 
		Value Description 
		0 Returns only those jobs that are not idle or suspended.  
		1 Executing. 
		2 Waiting for thread. 
		3 Between retries. 
		4 Idle. 
		5 Suspended. 
		7 Performing completion actions 

	*/
	
	declare	@job_id UNIQUEIDENTIFIER 
		, @is_sysadmin INT
		, @job_owner   sysname

	select @job_id = job_id from msdb..sysjobs_view where name = @job_name 
	select @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
	select @job_owner = SUSER_SNAME()

	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, -- BOOL
	                            request_source        INT              NOT NULL,
	                            request_source_id     sysname          COLLATE database_default NULL,
	                            running               INT              NOT NULL, -- BOOL
	                            current_step          INT              NOT NULL,
	                            current_retry_attempt INT              NOT NULL,
	                            job_state             INT              NOT NULL)


	IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
		    INSERT INTO #xp_results
		    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
  	ELSE
		    INSERT INTO #xp_results
		    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner


	--declare @execution_status int
	set @execution_status = (select job_state from #xp_results)

	drop table #xp_results

	if @select_data =1 
		select @job_name as 'job_name', @execution_status as 'execution_status'

	set nocount off

	


Side Note:
sp_help_job - Returns information about jobs that are used by SQLServerAgent service to perform automated activities in Microsoft SQL Server.

Syntax:
sp_help_job [ [ @job_id = ] job_id ]
[ , [ @job_name = ] 'job_name' ]
[ , [ @job_aspect = ] 'job_aspect' ]
[ , [ @job_type = ] 'job_type' ]
[ , [ @owner_login_name = ] 'login_name' ]
[ , [ @subsystem = ] 'subsystem' ]
[ , [ @category_name = ] 'category' ]
[ , [ @enabled = ] enabled ]
[ , [ @execution_status = ] status ]
[ , [ @date_comparator = ] 'date_comparison' ]
[ , [ @date_created = ] date_created ]
[ , [ @date_last_modified = ] date_modified ]
[ , [ @description = ] 'description_pattern' ]



Article keywords: create procedure sp_get_SQL_job_execution_status, UNIQUEIDENTIFIER, ISNULL, IS_SRVROLEMEMBER, SUSER_SNAME, INSERT INTO, master.dbo.xp_sqlagent_enum_jobs, set nocount on


Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to [email protected]    Greg Dubinovsky � 2006
or share your thoughts at Siccolo Blog

web sponsor - siccolo.com. well being sponsor - Enabling clinical and operational value across the continuum of care.
SQL Server Agent job that is considered running may be, executing, waiting for a thread, between retries, or performing completion actions. Therefore, any SQL Server Agent job that has a status associated with any of the statuses mentioned would be considered a running SQL Server Agent job. The sp_help_job stored procedure can be used to display all SQL Agent jobs that are not-idle, or suspended by setting the @execution_status to "0" as a parameter to this SP. The sp_help_job command returns job information for all jobs that are running (have a status of 1, 2, 3, or 7):
exec msdb.dbo.sp_help_job @execution_status = 0
This command is useful in identifying all running jobs. But what if you want to return the job information for a single job? To accomplish this, the sp_help_job stored procedure has two different parameters that can be used to identify a job. If you want to identify a job by its name you would use the "@job_name" parameter, or if you what to use the job identification number then you would use the "@job_id" parameter. For example of how you would return the job information for jobs based on the job name:
exec msdb.dbo.sp_help_job @job_name = 'Siccolo Development'