Detecting The State of a SQL Server Agent Job - Page 2

The "sp_help_job" SP has a parameter named "@execution_status" that can be used to return information for all jobs that have a particular status. Here is a command that displays job information for all jobs that currently have a status of "Executing":

exec msdb.dbo.sp_help_job 
  @execution_status = 1

Now in reality a 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 I just mentioned would be considered a running SQL Server Agent job. The "sp_help_job" SP 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 below 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" SP 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. Here is an example of how you would return the job information for jobs based on the job name:

exec msdb.dbo.sp_help_job @job_name = 'Copy Backups' 

The above command returns multiple record sets, where as all the prior examples only returned a single record set. The additional record sets contain step and schedule information for the specified job.

If you what to use the status for a SQL Server Agent job reported by "sp_help_job" SP in an automated process you might think you could just use the INSERT into <TableName> EXEC msdb.dbo.sp_help_job command syntax to get the output of this SP into a user table. If you try to use this method, you will find that you get the following error:

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67

An INSERT EXEC statement cannot be nested.

This is because the code behind sp_help_job performs an "INSERT EXEC" statement and you are not allowed to nest these statements. There are alternatives. In the next section, I will review one alternative that can be used to get the status for a particular job into a local variable.

Using xp_sqlagent_enum_jobs

If you review the code behind the "sp_help_job" SP, you will find an undocumented extended stored procedure (XP) "xp_sqlagent_enum_jobs." This XP can be used to return job information. The "xp_sqlagent_enum_job" XP can be found in the master database. This XP 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. Here is the syntax for using this XP:

xp_sqlagent_enum_jobs <is sysadmin (0 or 1)>, 
                      <job owner name> 
                      [, <job id>]

The first parameter identifies whether you want to return information about all jobs on the server, or just jobs owned by a particular job owner. If you specify "0" for this first parameter, it means you want to return job information for a particular job owner. If you specify a "1," it means you want information for all jobs. The second parameter identifies the job owner. This parameter is required on all calls to this XP but is only used when you specify "0" for the first parameter. The third and last parameter only needs to be provided if you want to return information about a particular job_id.

Below is a table that shows the different columns returned in the record set produced by the XP.

Column Name	Data type
Job_ID	uniqueidentifier
Last_Run_Date	Int
Last_Run_Time	Int
Next_Run_Date	Int
Next_Run_Time	Int
Next_Run_Schedule_ID	Int
Requested_To_Run	Int
Request_Source	Int
Request_Source_ID	varchar(100)
Running	Int
Current_Step	Int
Current_Retry_Attempt	Int
State	Int

If you are going to use this XP to determine whether a SQL Agent job is running then you would want to review the values returned in the "State" column. The "State" column has the same meaning as the "currect_execution_status" column returned from the sp_help_job SP. You would use the "Current_Step" column to identify which step in the job is currently running.

Let me show you a couple of different examples of how to call the "xp_sqlagent_enum_jobs" XP. This first example places job information for all jobs on the SQL Server instance into a temporary table named "#enum_job":

create table #enum_job ( 
Job_ID uniqueidentifier, 
Last_Run_Date int, 
Last_Run_Time int, 
Next_Run_Date int, 
Next_Run_Time int, 
Next_Run_Schedule_ID int, 
Requested_To_Run int, 
Request_Source int, 
Request_Source_ID varchar(100), 
Running int, 
Current_Step int, 
Current_Retry_Attempt int, 
State int 
)       
insert into #enum_job 
     exec master.dbo.xp_sqlagent_enum_jobs 1,garbage  
select * from #enum_job
drop table #enum_job

Above, I have created a temp table #enum_job and then used the INSERT EXEC command syntax to populate this table. Note that I passed "garbage" for the value of the job owner parameter. Because I wanted to return all job information this parameter is required but not used to identify the final outcome of the "xp_sqlagent_enum_job".

This second example returns the job information for all jobs owned by owner "GREGL." Here is the command to return that information:

exec master.dbo.xp_sqlagent_enum_jobs 0,GREGL

Note that the first parameter is now set to "0," and the second parameter is set to "GREGL." Because the first parameter is set to "0," the second parameter will now be used to return job information for all jobs owned by "GREGL."

Say you want to return job information for a particular job identification number. To do this you would use the following code to return that information:

declare @job_id uniqueidentifier 
set @job_id = 'F8DC5309-5A36-4F49-BF0B-2084F05277C0'     
exec master.dbo.xp_sqlagent_enum_jobs 0,GREGL,@job_id   

Here, I have identified the job_id for a job owned by "GREGL." The above execution of "xp_sqlagent_enum_jobs" only returns information about that single job. Note that I have specified a "0" for the first parameter. When passing a unique identifier to this XP you can specify either a "0" or a "1."

Conclusion

It is too bad you cannot use the "INSERT EXEC" statement to get the output of a SP into a table if the SP contains an "INSERT EXEC" statement. Because of this limitation, sometimes you have to dig into those Microsoft provided SP's to find those undocumented methods of obtaining system data. If you want to get the current status of a SQL Server Agent job into a local variable, you are going to have to develop some code that utilizes the output of the undocumented XP "xp_sqlagent_enum_jobs". Next month, I will show you how to use the "xp_sqlagent_enum_jobs" XP to help identify long running jobs.

» See All Articles by Columnist Gregory A. Larsen