TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sp_help_job (Transact-SQL)

 

Returns information about jobs that are used by SQL Server Agent to perform automated activities in SQL Server.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

  
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' ]  

[ @job_id =] job_id
The job identification number. job_id is uniqueidentifier, with a default of NULL.

[ @job_name =] 'job_name'
The name of the job. job_name is sysname, with a default of NULL.

System_CAPS_ICON_note.jpg Note


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

[ @job_aspect =] 'job_aspect'
The job attribute to display. job_aspect is varchar(9), with a default of NULL, and can be one of these values.

ValueDescription
ALLJob aspect information
JOBJob information
SCHEDULESSchedule information
STEPSJob step information
TARGETSTarget information

[ @job_type =] 'job_type'
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'
The login name of the owner of the job. login_name is sysname, with a default of NULL.

[ @subsystem =] 'subsystem'
The name of the subsystem. subsystem is nvarchar(40), with a default of NULL.

[ @category_name =] 'category'
The name of the category. category is sysname, with a default of NULL.

[ @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
The execution status for the jobs. status is int, with a default of NULL, and can be one of these values.

ValueDescription
0Returns only those jobs that are not idle or suspended.
1Executing.
2Waiting for thread.
3Between retries.
4Idle.
5Suspended.
7Performing completion actions.

[ @date_comparator =] 'date_comparison'
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
The date the job was created. date_createdis datetime, with a default of NULL.

[ @date_last_modified =] date_modified
The date the job was last modified. date_modified is datetime, with a default of NULL.

[ @description =] 'description_pattern'
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.

0 (success) or 1 (failure)

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

Column nameData typeDescription
job_iduniqueidentifierUnique ID of the job.
originating_servernvarchar(30)Name of the server from which the job came.
namesysnameName of the job.
enabledtinyintIndicates whether the job is enabled to be executed.
descriptionnvarchar(512)Description for the job.
start_step_idintID of the step in the job where execution should begin.
categorysysnameJob category.
ownersysnameJob owner.
notify_level_eventlogintBitmask indicating under what circumstances a notification event should be logged to the Microsoft Windows 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_emailintBitmask 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_netsendintBitmask 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_pageintBitmask 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_operatorsysnameE-mail name of the operator to notify.
notify_netsend_operatorsysnameName of the computer or user used when sending network messages.
notify_page_operatorsysnameName of the computer or user used when sending a page.
delete_levelintBitmask indicating under what circumstances the job should be deleted when a job completes. Possible values are the same as for notify_level_eventlog.
date_createddatetimeDate the job was created.
date_modifieddatetimeDate the job was last modified.
version_numberintVersion of the job (automatically updated each time the job is modified).
last_run_dateintDate the job last started execution.
last_run_timeintTime the job last started execution.
last_run_outcomeintOutcome of the job the last time it ran:

 0 = Failed

 1 = Succeeded

 3 = Canceled

 5 = Unknown
next_run_dateintDate the job is scheduled to run next.
next_run_timeintTime the job is scheduled to run next.
next_run_schedule_idintIdentification number of the next run schedule.
current_execution_statusintCurrent execution status.
current_execution_stepsysnameCurrent execution step in the job.
current_retry_attemptintIf the job is running and the step has been retried, this is the current retry attempt.
has_stepintNumber of job steps the job has.
has_scheduleintNumber of job schedules the job has.
has_targetintNumber of target servers the job has.
typeintType of the job.

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 nameData typeDescription
step_idintUnique (for this job) identifier for the step.
step_namesysnameName of the step.
subsystemnvarchar(40)Subsystem in which to execute the step command.
commandnvarchar(3200)Command to execute.
flagsnvarchar(4000)Bitmask of values that control step behavior.
cmdexec_success_codeintFor a CmdExec step, this is the process exit code of a successful command.
on_success_actionnvarchar(4000)What to do if the step succeeds:

 1 = Quit with success.

 2 = Quit with failure.

 3 = Go to next step.

 4 = Go to step.
on_success_step_idintIf on_success_action is 4, this indicates the next step to execute.
on_fail_actionnvarchar(4000)Action to take if the step fails. Values are the same as for on_success_action.
on_fail_step_idintIf on_fail_action is 4, this indicates the next step to execute.
serversysnameReserved.
database_namesysnameFor a Transact-SQL step, this is the database in which the command will executes.
database_user_namesysnameFor a Transact-SQL step, this is the database user context in which the command executes.
retry_attemptsintMaximum number of times the command should be retried (if it is unsuccessful) before the step is deemed to have failed.
retry_intervalintInterval (in minutes) between any retry attempts.
os_run_priorityvarchar(4000)Reserved.
output_file_namevarchar(200)File to which command output should be written (Transact-SQL and CmdExec steps only).
last_run_outcomeintOutcome of the step the last time it ran:

 0 = Failed

 1 = Succeeded

 3 = Canceled

 5 = Unknown
last_run_durationintDuration (in seconds) of the step the last time it ran.
last_run_retriesintNumber of times the command was retried the last time the step ran.
last_run_dateintDate the step last started execution.
last_run_timeintTime the step last started execution.
proxy_idintProxy for the job step.

This is the result set for job schedules.

Column nameData typeDescription
schedule_idintIdentifier of the schedule (unique across all jobs).
schedule_namesysnameName of the schedule (unique for this job only).
enabledintWhether the schedule is active (1) or not (0).
freq_typeintValue indicating when the job is to be executed:

 1 = Once

 4 = Daily

 8 = Weekly

 16 = Monthly

 32 = Monthly, relative to the freq_interval

 64 = Run when SQLServerAgent service starts.
freq_intervalintDays when the job is executed. The value depends on the value of freq_type. For more information, see sp_add_schedule (Transact-SQL)
freq_subday_typeIntUnits for freq_subday_interval. For more information, see sp_add_schedule (Transact-SQL)
freq_subday_intervalintNumber of freq_subday_type periods to occur between each execution of the job. For more information, see sp_add_schedule (Transact-SQL)
freq_relative_intervalintScheduled job's occurrence of the freq_interval in each month. For more information, see sp_add_schedule (Transact-SQL)
freq_recurrence_factorintNumber of months between the scheduled execution of the job.
active_start_dateintDate to begin execution of the job.
active_end_dateintDate to end execution of the job.
active_start_timeintTime to begin the execution of the job on active_start_date.
active_end_timeintTime to end execution of the job on active_end_date.
date_createddatetimeDate the schedule is created.
schedule_descriptionnvarchar(4000)An English description of the schedule (if requested).
next_run_dateintDate the schedule will next cause the job to run.
next_run_timeintTime the schedule will next cause the job to run.
schedule_uiduniqueidentifierIdentifier for the schedule.
job_countintReturns the number of jobs that reference this schedule.

This is the result set for job target servers.

Column nameData typeDescription
server_idintIdentifier of the target server.
server_namenvarchar(30)Computer name of the target server.
enlist_datedatetimeDate the target server enlisted into the master server.
last_poll_datedatetimeDate the target server last polled the master server.
last_run_dateintDate the job last started execution on this target server.
last_run_timeintTime the job last started execution on this target server.
last_run_durationintDuration of the job the last time it ran on this target server.
last_run_outcometinyintOutcome of the job the last time it ran on this server:

 0 = Failed

 1 = Succeeded

 3 = Canceled

 5 = Unknown
last_outcome_messagenvarchar(1024)Outcome message from the job the last time it ran on this target server.

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

  • SQLAgentUserRole

  • SQLAgentReaderRole

  • SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Members of SQLAgentUserRole can only view jobs that they own. Members of sysadmin, SQLAgentReaderRole, and SQLAgentOperatorRole can view all local and multiserver jobs.

A. 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 msdb database.

USE msdb ;  
GO  
  
EXEC dbo.sp_help_job ;  
GO  

B. Listing information for jobs matching a specific criteria

The following example lists job information for the multiserver jobs owned by françoisa where the job is enabled and executing.

USE msdb ;  
GO  
  
EXEC dbo.sp_help_job   
   @job_type = N'MULTI-SERVER',  
   @owner_login_name = N'françoisa',  
   @enabled = 1,  
   @execution_status = 1 ;  
GO  

C. Listing all aspects of information for a job

The following example lists all aspects of the information for the job NightlyBackups.

USE msdb ;  
GO  
  
EXEC dbo.sp_help_job  
    @job_name = N'NightlyBackups',  
    @job_aspect = N'ALL' ;  
GO  

sp_add_job (Transact-SQL)
sp_delete_job (Transact-SQL)
sp_update_job (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft