Skip to content

Latest commit

 

History

History
123 lines (82 loc) · 3.7 KB

sp-delete-schedule-transact-sql.md

File metadata and controls

123 lines (82 loc) · 3.7 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_delete_schedule (Transact-SQL)
sp_delete_schedule deletes a schedule.
markingmyname
maghan
randolphwest
01/23/2024
sql
system-objects
reference
sp_delete_schedule
sp_delete_schedule_TSQL
sp_delete_schedule
TSQL

sp_delete_schedule (Transact-SQL)

[!INCLUDE SQL Server]

Deletes a schedule.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_delete_schedule
    [ [ @schedule_id = ] schedule_id ]
    [ , [ @schedule_name = ] N'schedule_name' ]
    [ , [ @force_delete = ] force_delete ]
    [ , [ @automatic_post = ] automatic_post ]
[ ; ]

Arguments

[ @schedule_id = ] schedule_id

The schedule identification number of the schedule to delete. @schedule_id is int, with a default of NULL.

Either @schedule_id or @schedule_name must be specified, but both can't be specified.

[ @schedule_name = ] N'schedule_name'

The name of the schedule to delete. @schedule_name is sysname, with a default of NULL.

Either @schedule_id or @schedule_name must be specified, but both can't be specified.

[ @force_delete = ] force_delete

Specifies whether the procedure should fail if the schedule is attached to a job. @force_delete is bit, with a default of 0.

  • When @force_delete is 0, the stored procedure fails if the schedule is attached to a job.
  • When @force_delete is 1, the schedule is deleted regardless of whether the schedule is attached to a job.

[ @automatic_post = ] automatic_post

[!INCLUDE ssinternalonly-md]

Return code values

0 (success) or 1 (failure).

Result set

None.

Remarks

By default, a schedule can't be deleted if the schedule is attached to a job. To delete a schedule that is attached to a job, specify a value of 1 for @force_delete. Deleting a schedule doesn't stop jobs that are currently running.

Permissions

[!INCLUDE msdb-execute-permissions]

Other users must be granted one of the following [!INCLUDE ssNoVersion] Agent fixed database roles in the msdb database:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

The job owner can attach a job to a schedule and detach a job from a schedule without also having to be the schedule owner. However, a schedule can't be deleted if the detach would leave it with no jobs, unless the caller is the schedule owner.

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

Only members of the sysadmin role can delete a job schedule that is owned by another user.

Examples

A. Delete a schedule

The following example deletes the schedule NightlyJobs. If the schedule is attached to any job, the example doesn't delete the schedule.

USE msdb;
GO

EXEC dbo.sp_delete_schedule
    @schedule_name = N'NightlyJobs';
GO

B. Delete a schedule attached to a job

The following example deletes the schedule RunOnce, regardless of whether the schedule is attached to a job.

USE msdb;
GO

EXEC dbo.sp_delete_schedule
    @schedule_name = 'RunOnce',
    @force_delete = 1;
GO

Related content