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_jobThis 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 |
| 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 |
| 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. |