Featured Database Articles

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:

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:

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":

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:

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.