sp_help_jobschedule
Returns information about the scheduling of jobs used by SQL Server Enterprise Manager to perform automated activities.
Syntax
sp_help_jobschedule
[ @job_id =] job_id |
[ @job_name = ] 'job_name'
[ , [ @schedule_name = ] 'schedule_name' ] |
[ , [ @schedule_id = ] schedule_id ]
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 varchar(100), with a default of NULL.
Note Either job_id or job_name must be specified, but both cannot be specified.
[@schedule_name =] 'schedule_name'
Is the name of the schedule item for the job. schedule_name is varchar(100), with a default of NULL.
{[@schedule_id =] schedule_id
Is the identification number of the schedule item for the job. schedule_id is int, with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Result Sets
| Column name | Data type | Description |
|---|---|---|
| schedule_id | int | Schedule identifier number. |
| schedule_name | sysname | Name of the schedule. |
| enabled | int | Whether the schedule enabled (1) or not enabled (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 the schedule is activated. |
| active_end_date | int | End date of the schedule. |
| active_start_time | int | Time of the day the schedule starts. |
| active_end_time | int | Time of the day schedule ends. |
| date_created | datetime | Date the schedule is created. |
| schedule_description | nvarchar(4000) | An English description of the schedule (if scheduled). |
| 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. |
Remarks
The parameters of sp_help_jobschedule can be used only in certain combinations. If schedule_id is specified, neither job_id nor job_name can be specified. Otherwise, the job_id or job_name parameters can be used with schedule_name.
Permissions
Execute permissions default to the public role. Anyone who can execute this procedure can also create, delete, or update a job, job step, job category, job schedule, job server, task, or job history information.
Examples
A. Return the job schedule for a specific job
This example returns the scheduling information for a job named Archive Tables.
USE msdb
EXEC sp_help_jobhistory @job_name = 'Archive Tables'
B. Return the job schedule for a named item in the schedule
This example returns the history for a job named Archive Tables and for its schedule item Weekly Archive.
USE msdb
EXEC sp_help_jobhistory @job_name = 'Archive Tables',
@schedule_name = 'Weekly Archive'