sp_help_jobstep

Returns information for the steps in a job.

Syntax

sp_help_jobstep { [ @job_id = ] 'job_id' | [ @job_name = ] 'job_name' }
     [ , [ @step_id = ] step_id ]
     [ , [ @step_name = ] 'step_name' ]
     [ , [ @suffix = ] suffix ]    /* 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

Either job_id or job_name must be specified, but both cannot be specified.

[ @step_id = ] step_id

The identification number of the step in the job. If not included, all steps in the job are included. step_id is int, with a default of NULL.

[ @step_name = ] _’step_name’_

The name of the step in the job. _step_name_ is sysname, with a default of NULL.

[ @suffix = ] _suffix_

Not supported.

Result Sets

Column name Data type Description
step_id int Unique identifier for the step.
step_name sysname Name of the step in the job.
subsystem nvarchar(40) Not supported.
command nvarchar(max) Command executed in the step.
flags int A bitmask of values that control step behavior.
cmdexec_success_code int Not supported.
on_success_action tinyint Action to take if the step succeeds: 1 = Quit the job reporting success, 2 = Quit the job reporting failure, 3 = Go to the next step, 4 = Go to step.
on_success_step_id int If _on_success_action_ is 4, this indicates the next step to execute.
on_fail_action tinyint What to do if the step fails. Values are same as _on_success_action_.
on_fail_step_id int If _on_fail_action_ is 4, this indicates the next step to execute.
server sysname Not supported.
database_name sysname The database in which the command executes.
database_user_name sysname The database user context in which the command executes.
retry_attempts int Not supported.
retry_interval int Not supported.
os_run_priority int Not supported.
output_file_name nvarchar(200) Not supported.
last_run_outcome int Outcome of the step the last time it ran: 0 = Failed, 1 = Succeeded, 2 = Retry, 3 = Canceled, 5 = Unknown
last_run_duration int Duration (in seconds) of the step the last time it ran.
last_run_retries int Not supported.
last_run_date int Date the step last started execution.
last_run_time int Time the step last started execution.
proxy_id int Not supported.

Permissions

To run this stored procedure, users must be a member of the sysadmin fixed server role.

Examples

  1. Return information for all steps in a specific job.

The following example returns all the job steps for the job named “RebuildCache”.

EXEC dbo.sp_help_jobstep
    @job_name = N'RebuildCache';
  1. Return information about a specific job step.
EXEC dbo.sp_help_jobstep
    @job_name = N'RebuildCache',
    @step_id = 1 ;