Detecting The State of a SQL Server Agent Job

March 31, 2005

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.

Go to page: 1 2 Next

Detecting The State of a SQL Server Agent Job

Why Would you want to do this?

Using Enterprise Manager

Using sp_help_job

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos