sp_help_jobschedule (Transact-SQL)

  • Article

Returns information about the scheduling of jobs used by SQL Server Management Studio to perform automated activities.

Syntax

sp_help_jobschedule { [ @job_id = ] job_id | [ @job_name = ] 'job_name' }
     [ , [ @schedule_name = ] 'schedule_name' ]
     [ , [ @schedule_id = ] schedule_id ]
     [ , [ @include_description = ] include_description ]

Arguments

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

  • [ @job_name= ] 'job_name'
    The name of the job. job_nameis 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_nameis sysname, with a default of NULL.
  • [ @schedule_id= ] schedule_id
    The identification number of the schedule item for the job. schedule_idis 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.

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

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.

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

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.

Examples

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

See Also

Reference

sp_add_schedule (Transact-SQL)
sp_delete_schedule (Transact-SQL)
sp_update_schedule (Transact-SQL)
System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance