Edit

Share via

sp_delete_job (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

Deletes a job from the SQL Server Agent service.

Transact-SQL syntax conventions

Syntax

sp_delete_job
    [ [ @job_id = ] 'job_id' ]
    [ , [ @job_name = ] N'job_name' ]
    [ , [ @originating_server = ] N'originating_server' ]
    [ , [ @delete_history = ] delete_history ]
    [ , [ @delete_unused_schedule = ] delete_unused_schedule ]
[ ; ]

Arguments

[ @job_id = ] 'job_id'

The identification number of the job to be deleted. @job_id is uniqueidentifier, with a default of NULL.

Either @job_id or @job_name must be specified; both can't be specified.

[ @job_name = ] N'job_name'

The name of the job to be deleted. @job_name is sysname, with a default of NULL.

Either @job_id or @job_name must be specified; both can't be specified.

[ @originating_server = ] N'originating_server'

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

[ @delete_history = ] delete_history

Specifies whether to delete the history for the job. @delete_history is bit, with a default of 1.

  • When @delete_history is 1, the job history for the job is deleted.
  • When @delete_history is 0, the job history isn't deleted.

When a job is deleted and the history isn't deleted, historical information for the job doesn't display in the SQL Server Agent graphical user interface job history, but the information still resides in the sysjobhistory table in the msdb database.

[ @delete_unused_schedule = ] delete_unused_schedule

Specifies whether to delete the schedules attached to this job if they aren't attached to any other job. @delete_unused_schedule is bit, with a default of 1.

  • When @delete_unused_schedule is 1, schedules attached to this job are deleted if no other jobs reference the schedule.
  • When @delete_unused_schedule is 0, the schedules aren't deleted.

Return code values

0 (success) or 1 (failure).

Result set

None.

Remarks

The @originating_server argument is reserved for internal use.

The @delete_unused_schedule argument provides backward compatibility with previous versions of SQL Server by automatically removing schedules that aren't attached to any job. This parameter defaults to the backward-compatible behavior. To retain schedules that aren't attached to a job, you must provide the value 0 as the @delete_unused_schedule argument.

SQL Server Management Studio provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.

This stored procedure can't delete maintenance plans, and can't delete jobs that are part of maintenance plans. Instead, use SQL Server Management Studio to delete maintenance plans.

This stored procedure shares the name of sp_delete_job with a similar object for the Azure Elastic Jobs service for Azure SQL Database. For information about the elastic jobs version, see jobs.sp_delete_job (Azure Elastic Jobs).

Permissions

This stored procedure is owned by the db_owner role. You can grant EXECUTE permissions for any user, but these permissions may be overridden during a SQL Server upgrade.

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 the sysadmin fixed server role can execute sp_delete_job to delete any job. A user that isn't a member of the sysadmin fixed server role can only delete jobs owned by that user.

Examples

The following example deletes the job NightlyBackups.

USE msdb;
GO

EXEC sp_delete_job
    @job_name = N'NightlyBackups';
GO

Related content