Detecting The State of a SQL Server Agent Job

Thursday Mar 31st 2005 by Gregory A. Larsen
Share:

How do you determine if a SQL Server Agent Job is running, is there a way to automate the process, is there an easy way to determine all of the SQL Server Agent jobs that are currently running? Greg Larsen answers these questions, showing different methods that determine the state of an individual job, or all SQL Server Agent jobs.

How do you determine if a SQL Server Agent Job is running? Is there a way to automate the process of determining if a SQL Server Agent job is running? What automated approach is there to determine what step is being processed for a given SQL Server Agent job? Is there an easy way to determine all of the SQL Server Agent jobs that are currently running? This article will show you a couple of different methods to determine the state of an individual job, or all SQL Server Agent jobs.

Why Would you want to do this?

So why would someone what this information? Maybe you just want to know if any SQL Server Agent jobs are running, before you shutdown SQL Server. Or, possibly you want to start a particular SQL Server Agent job, but want to make sure that the jobs is not already running, prior to submitting the job again. Possible you want to know what step is currently executing for a particular running SQL Server Agent job. Maybe you have an automated process that needs to be able to detect if a job is running or not, so it can perform some action.

Using Enterprise Manager

I am sure most are familiar with using Enterprise Manager to review the current status of all SQL Server agent jobs. For this reason, I am not going to spend much time discussing this option. To use Enterprise Manager, you just need to expand the server folder, and then expand the "Management" folder, followed by expanding the "SQL Server Agent" folder and finally expanding the "Jobs" folder. After doing this you can use the "Status" column to determine whether a job is running, and what step is currently executing.

Using sp_help_job

The stored procedure (SP) "sp_help_job" is a SP that can be used to return SQL Server Agent job information. This SP comes with SQL Server and is stored in the "msdb" database. This SP returns a different record set depending on what parameters are passed. I am not going to touch on all of the different ways to call this SP, so if you are interested in the complete functionality of this SP, I would suggest you review the information about this SP in the "SQL Server Books Online" documentation. What I am going to touch on is how to use the sp_help_job to return information about the status of a single SQL Agent job, or all SQL Agent jobs on an instance of SQL Server.

To return the SQL Server Agent job information for all jobs on a server you can use the following command:

exec msdb.dbo.sp_help_job

This command returns a record set that contains one row for each SQL Server Agent job. The table below explains what each column represents in the returned record set:

Column name

Data type

Description

job_id

uniqueidentifier

Unique ID of the job.

originating_server

nvarchar(30)

Name of the server from which the job came.

Name

sysname

Name of the job.

Enabled

tinyint

Indicates whether the job is enabled to be executed.

Description

nvarchar(512)

Description for the job.

start_step_id

Int

ID of the step in the job where execution should begin.

Category

sysname

Job category.

Owner

sysname

Job owner.

notify_level_eventlog

Int

Bitmask indicating under what circumstances a notification event should be logged to the Microsoft Windows NT. application log. Can be one of these values:

0 = Never
1 = When a job succeeds
2 = When the job fails
3 = Whenever the job completes (regardless of the job outcome)

notify_level_email

Int

Bitmask indicating under what circumstances a notification e-mail should be sent when a job completes. Possible values are the same as for notify_level_eventlog.

notify_level_netsend

Int

Bitmask indicating under what circumstances a network message should be sent when a job completes. Possible values are the same as for notify_level_eventlog.

notify_level_page

Int

Bitmask indicating under what circumstances a page should be sent when a job completes. Possible values are the same as for notify_level_eventlog.

notify_email_operator

sysname

E-mail name of the operator to notify.

notify_netsend_operator

sysname

Name of the computer or user used when sending network messages.

notify_page_operator

sysname

Name of the computer or user used when sending a page.

delete_level

Int

Bitmask indicating under what circumstances the job should be deleted when a job completes. Possible values are the same as for notify_level_eventlog.

date_created

datetime

Date the job was created.

date_modified

datetime

Date the job was last modified.

version_number

Int

Version of the job (automatically updated each time the job is modified).

last_run_date

Int

Date the job last started execution.

last_run_time

Int

Time the job last started execution.

last_run_outcome

Int

Outcome of the job the last time it ran:

0 = Failed
1 = Succeeded
3 = Canceled
5 = Unknown

next_run_date

Int

Date the job is scheduled to run next.

next_run_time

Int

Time the job is scheduled to run next.

next_run_schedule_id

Int

Identification number of the next run schedule.

current_execution_status

Int

Current execution status.

current_execution_step

Sysname

Current execution step in the job.

current_retry_attempt

Int

If the job is running and the step has been retried, this is the current retry attempt.

has_step

Int

Number of job steps the job has.

has_schedule

Int

Number of job schedules the job has.

has_target

Int

Number of target servers the job has.

Type

Int

1 = Local job.
2 = Multiserver job.
0 = Job has no target servers.

The table above is from the "SQL Server Books Online" documentation. Note that there is a column returned named "current_execution_status" which identifies the current status of each job. Below is a table from the "SQL Server Books Online" documentation that describes the meaning of each status:

Value

Description

1

Executing.

2

Waiting for thread.

3

Between retries.

4

Idle.

5

Suspended.

7

Performing completion actions.

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved