sp_help_job
Returns information about jobs that are used by SQLServerAgent service to perform automated activities in Microsoft® SQL Server™.
Syntax
sp_help_job [ [ @job_id = ] job_id ]
[ , [ @job_name = ] 'job_name' ]
[ , [ @job_aspect = ] 'job_aspect' ]
[ , [ @job_type = ] 'job_type' ]
[ , [ @owner_login_name = ] 'login_name' ]
[ , [ @subsystem = ] 'subsystem' ]
[ , [ @category_name = ] 'category' ]
[ , [ @enabled = ] enabled ]
[ , [ @execution_status = ] status ]
[ , [ @date_comparator = ] 'date_comparison' ]
[ , [ @date_created = ] date_created ]
[ , [ @date_last_modified = ] date_modified ]
[ , [ @description = ] 'description_pattern' ]
Arguments
[@job_id =] job_id
Is the job identification number. job_id is uniqueidentifier, with a default of NULL.
[@job_name =] 'job_name'
Is the name of the job. job_name is sysname, with a default of NULL.
Note Either job_id or job_name must be specified, but both cannot be specified.
[@job_aspect =] 'job_aspect'
Is the job attribute to display. job_aspect is varchar(9), with a default of NULL, and can be one of these values.
| Value | Description |
|---|---|
| ALL | Job aspect information |
| JOB | Job information |
| SCHEDULES | Schedule information |
| STEPS | Job step information |
| TARGETS | Target information |
[@job_type =] 'job_type'
Is the type of jobs to include in the report. job_type is varchar(12), with a default of NULL. job_type can be LOCAL or MULTI-SERVER.
[@owner_login_name =] 'login_name'
Is the login name of the owner of the job. login_name is sysname, with a default of NULL.
[@subsystem =] 'subsystem'
Is the name of the subsystem. subsystem is nvarchar(60), with a default of NULL.
[@category_name =] 'category'
Is the name of the category. category is sysname, with a default of NULL.
[@enabled =] enabled
Is 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
Is the execution status for the jobs. status is int, with a default of NULL, and can be one of these values.
| Value | Description |
|---|---|
| 0 | Returns only those jobs that are not idle or suspended. |
| 1 | Executing. |
| 2 | Waiting for thread. |
| 3 | Between retries. |
| 4 | Idle. |
| 5 | Suspended. |
| 7 | Performing completion actions. |
[@date_comparator =] 'date_comparison'
Is the comparison operator to use in comparisons of date_created and date_modified. date_comparison is char(1), and can be =, <, or >.
[@date_created =] date_created
Is the date the job was created. date_created is datetime, with a default of NULL.
[@date_last_modified =] date_modified
Is the date the job was last modified. date_modified is datetime, with a default of NULL.
[@description =] 'description_pattern'
Is the description of the job. description_pattern is nvarchar(512), with a default of NULL. description_pattern can include the SQL Server wildcard characters for pattern matching.
Return Code Values
0 (success) or 1 (failure)
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) | 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. |
If job_id or job_name is specified, sp_help_job returns these additional result sets for job steps, job schedules, and job target servers.
This is the result set for job steps.
| Column name | Data type | Description |
|---|---|---|
| step_id | int | Unique (for this job) identifier for the step. |
| step_name | sysname | Name of the step. |
| Subsystem | nvarchar(40) | Subsystem in which to execute the step command. |
| Command | nvarchar(3200) | Command to execute. |
| Flags | nvarchar(4000) | Bitmask of values that control step behavior. |
| cmdexec_success_code | int | For a CmdExec step, this is the process exit code of a successful command. |
| on_success_action | nvarchar(4000) | What to do if the step succeeds:
1 = Quit with success. |
| on_success_step_id | int | If on_success_action is 4, this indicates the next step to execute. |
| on_fail_action | nvarchar(4000) | Action to take if the step fails. Values are the same as for on_success_action. |
| on_fail_step_id | int | If on_fail_action is 4, this indicates the next step to execute. |
| Server | sysname | Reserved. |
| database_name | sysname | For a Transact=SQL step, this is the database in which the command will executes. |
| database_user_name | sysname | For a Transact=SQL step, this is the database user context in which the command executes. |
| retry_attempts | int | Maximum number of times the command should be retried (if it is unsuccessful) before the step is deemed to have failed. |
| retry_interval | int | Interval (in minutes) between any retry attempts. |
| os_run_priority | varchar(4000) | Reserved. |
| output_file_name | varchar(200) | File to which command output should be written (Transact=SQL and CmdExec steps only). |
| last_run_outcome | int | Outcome of the step the last time it ran:
0 = Failed |
| last_run_duration | int | Duration (in seconds) of the step the last time it ran. |
| last_run_retries | int | Number of times the command was retried the last time the step ran. |
| last_run_date | int | Date the step last started execution. |
| last_run_time | int | Time the step last started execution. |
This is the result set for job schedules.
| Column name | Data type | Description |
|---|---|---|
| schedule_id | int | Identifier of the schedule (unique across all jobs). |
| schedule_name | sysname | Name of the schedule (unique for this job only). |
| Enabled | int | Whether the schedule is active (1) or not (0). |
| freq_type | int | Value indicating when the job is to be executed:
1 = Once |
| freq_interval | int | Days when the job is executed. The value depends on the value of freq_type. For more information, see sp_add_jobschedule. |
| freq_subday_type | int | Units for freq_subday_interval. For more information, see sp_add_jobschedule. |
| freq_subday_interval | int | Number of freq_subday_type periods to occur between each execution of the job. For more information, see sp_add_jobschedule. |
| freq_relative_interval | int | Scheduled job's occurrence of the freq_interval in each month. For more information, see sp_add_jobschedule. |
| freq_recurrence_factor | int | Number of months between the scheduled execution of the job. |
| active_start_date | int | Date to begin execution of the job. |
| active_end_date | int | Date to end execution of the job. |
| active_start_time | int | Time to begin the execution of the job on active_start_date. |
| active_end_time | int | Time to end execution of the job on active_end_date. |
| date_created | datetime | Date the schedule is created. |
| schedule_description | nvarchar(4000) | An English description of the schedule (if requested). |
| next_run_date | int | Date the schedule will next cause the job to run. |
| next_run_time | int | Time the schedule will next cause the job to run. |
This is the result set for job target servers.
| Column name | Data type | Description |
|---|---|---|
| server_id | int | Identifier of the target server. |
| server_name | nvarchar(30) | Computer name of the target server. |
| enlist_date | datetime | Date the target server enlisted into the master server (MSX). |
| last_poll_date | datetime | Date the target server last polled the MSX. |
| last_run_date | int | Date the job last started execution on this target server. |
| last_run_time | int | Time the job last started execution on this target server. |
| last_run_duration | int | Duration of the job the last time it ran on this target server. |
| last_run_outcome | tinyint | Outcome of the job the last time it ran on this server:
0 = Failed |
| last_outcome_message | nvarchar(1024) | Outcome message from the job the last time it ran on this target server. |
Permissions
Execute permissions default to the public role in the msdb database. A user who can execute this procedure and is a member of the sysadmin fixed role can also create, delete, or update a job, job step, job category, job schedule, job server, task, or job history information. A user who is not a member of the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns.
When sp_help_job is invoked by a user who is a member of the sysadmin fixed server role, sp_help_job will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, sp_help_job will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, sp_help_job will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and sp_help_job is always executed under the security context of the Windows 9.x user who started SQL Server.
Examples
A. List information for all jobs
This example executes the sp_help_job procedure with no parameters to return the information for all of the jobs currently defined in the msdb database.
USE msdb
EXEC sp_help_job
B. List information for a specific job
This example lists all job aspect information for the multiserver job named Archive Tables, in which the job is enabled and has been modified since its creation.
USE msdb
EXEC sp_help_job NULL, 'Archive Tables', 'ALL', 'MULTI-SERVER', 'janetl',
NULL, NULL, 1, 1, '<', '12/01/97', '6/25/98', NULL