sp_help_jobschedule (Transact-SQL)
Returns information about the scheduling of jobs used by SQL Server Management Studio to perform automated activities.
- [ @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.
Note: Either job_id or job_name must be specified, but both cannot be specified.
- [ @schedule_name = ] 'schedule_name'
-
The name of the schedule item for the job. schedule_name is sysname, with a default of NULL.
- [ @schedule_id = ] schedule_id
-
The identification number of the schedule item for the job. schedule_id is int, with a default of NULL.
- [ @include_description = ] include_description
-
Specifies whether to include the description of the schedule in the result set. include_description is bit, with a default of 0. When include_description is 0, the description of the schedule is not included in the result set. When include_description is 1, the description of the schedule is included in the result set.
| 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 4 = Daily 8 = Weekly 16 = Monthly 32 = Monthly, relative to the freq_interval 64 = Run when SQLServerAgent service starts. |
|
freq_interval |
int |
Days 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_type |
int |
Units for freq_subday_interval. For more information, see sp_add_schedule (Transact-SQL). |
|
freq_subday_interval |
int |
Number of freq_subday_type periods to occur between each execution of the job. For more information, see sp_add_schedule (Transact-SQL). |
|
freq_relative_interval |
int |
Scheduled job's occurrence of the freq_interval in each month. For more information, see sp_add_schedule (Transact-SQL). |
|
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 derived from values in msdb.dbo.sysschedules. When include_description is 0, this column contains text stating that the description was not 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. |
|
schedule_uid |
uniqueidentifier |
Identifier for the schedule. |
|
job_count |
int |
Count of jobs returned. |
| Note: |
|---|
| sp_help_jobschedule returns values from the dbo.sysjobschedules and dbo.sysschedules system tables in msdb. Every 20 minutes, sysjobschedules updates. This can affect the values that are returned by this stored procedure. |
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 properties of job schedules that they own.
A. Return the job schedule for a specific job
The following example returns the scheduling information for a job named BackupDatabase.
USE msdb ;
GO
EXEC dbo.sp_help_jobschedule
@job_name = N'BackupDatabase' ;
GO
B. Return the job schedule for a specific schedule
The following example returns the information for the schedule named NightlyJobs and the job named RunReports.
USE msdb ;
GO
EXEC dbo.sp_help_jobschedule
@job_name = N'RunReports',
@schedule_name = N'NightlyJobs' ;
GO
C. Return the job schedule and schedule description for a specific schedule
The following example returns the information for the schedule named NightlyJobs and the job named RunReports. The result set returned includes a description of the schedule.
USE msdb ;
GO
EXEC dbo.sp_help_jobschedule
@job_name = N'RunReports',
@schedule_name = N'NightlyJobs',
@include_description = 1 ;
GO
