sp_help_job

Returns information about jobs.

Syntax

sp_help_job { [ @job_id = ] job_id
   [ @job_name = ] 'job_name' }
     [ , [ @job_aspect = ] 'job_aspect' ]        /* not supported */
     [ , [ @job_type = ] 'job_type' ]            /* not supported */
     [ , [ @owner_login_name = ] 'login_name' ]
     [ , [ @subsystem = ] 'subsystem' ]          /* not supported */
     [ , [ @category_name = ] 'category' ]       /* not supported */
     [ , [ @enabled = ] enabled ]
     [ , [ @execution_status = ] status ]        /* not supported */
     [ , [ @date_comparator = ] 'date_comparison' ]  /* not supported */
     [ , [ @date_created = ] date_created ]          /* not supported */
     [ , [ @date_last_modified = ] date_modified ]   /* not supported */
     [ , [ @description = ] 'description_pattern' ]  /* not supported */

Arguments

[ @job_id = ] ‘job_id’

The job identification number. The job_id is uniqueidentifier, with a default of NULL.

[ @job_name = ] ‘job_name’

The name of the job. The name must be unique. job_name is sysname, with a default of NULL.

Note

To view a specific job, either job_id or job_name must be specified. Omit both job_id and job_name to return information about all jobs.

[ @job_aspect = ] ‘job_aspect’ – not supported

The job attribute to display. _job_aspect_ is varchar(9), with a default of NULL. The parameter is not supported.

[ @job_type = ] ‘job_type’ – not supported

The type of jobs to include in the report. _job_type_ is varchar(12), with a default of NULL. The parameter is not supported.

[ @owner_login_name = ] ‘login_name’ ]

The login name of the owner of the job. _login_name_ is sysname, with a default of NULL.

[ @subsystem = ] ‘subsystem’ – not supported

The name of the subsystem. __subsystem__ is nvarchar(40), with a default of NULL. The parameter is not supported.

[ @category_name = ] ‘category’ – not supported

The name of the category. category is sysname, with a default of NULL. The parameter is not supported.

[ @enabled = ] enabled ]

A number indicating whether information is shown for enabled jobs or disabled jobs. enabled is tinyint, with a default of NULL. 1 indicates enabled jobs, and 0 indicates disabled jobs.

[ @execution_status = ] status – not supported

The execution status for the jobs. _status_ is int, with a default of NULL. The parameter is not supported.

[ @date_comparator = ] ‘date_comparison’ – not supported

The comparison operator to use in comparisons of _date_created_ and _date_modified_. The parameter is not supported.

[ @date_created = ] date_created – not supported

The date the job was created. _date_created_ is datetime, with a default of NULL. The parameter is not supported.

[ @date_last_modified = ] date_modified – not supported

The date the job was last modified. _date_modified_ is datetime, with a default of NULL. The parameter is not supported.

[ @description = ] ‘description_pattern’ – not supported

The description of the job. _description_pattern_ is nvarchar(512), with a default of NULL. The parameter is not supported.

Result Sets

If no arguments are specified, sp_help_job returns this result set.

Column name Data type Description
job_id uniqueidentifier Unique ID of the job.
originating_server nvarchar(30) Not supported.
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 Not supported.
owner sysname Job owner.
notify_level_eventlog int Not supported.
notify_level_email int Not supported.
notify_level_netsend int Not supported.
notify_level_page int Not supported.
notify_email_operator sysname Not supported.
notify_netsend_operator sysname Not supported.
notify_page_operator sysname Not supported.
delete_level int Bitmask indicating under what circumstances the job should be deleted when a job completes. Possible values are: 0 = Never, 1 = When a job succeeds, 2 = When the job fails, 3 = Whenever the job completes (regardless of the job outcome)
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 Not supported.
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 Not supported.
type int Not supported.

Permissions

By default, members of the sysadmin fixed server role can execute this stored procedure.

Examples

  1. List information for all jobs

The following example executes the sp_help_job procedure with no parameters to return the information for all of the jobs currently defined in the system.

EXEC dbo.sp_help_job ;