Deletes a job, a schedule, or a scheduled job.
By default, the name argument is the name or ID of
a scheduled job. To specify the name or ID for
a job or a schedule the name argument is prefixed with jname or sname.
sp_sjobdrop @name=’...’, @option=’...’
The name or ID of the scheduled job, job, or schedule to delete.
A list of options for the command:
all – used with a job or schedule to specify that all scheduled jobs owned by the caller that are using the specified job or schedule should also be deleted. When used with a scheduled job, it specifies that the job and schedule associated with the scheduled job should also be deleted, provided that no other scheduled jobs reference them.
all_users – allows a user with the js_admin_role to use sp_sjobdrop on scheduled jobs, jobs, and schedules that are owned by someone else.
show – displays the jobs, schedules, and scheduled jobs that will be deleted by this call to sp_sjobdrop, before any deleting occurs.
force – allows you to remove jobs from the database, even if they are currently running.
(See the Usage section for more details on these parameters.)
Returns 0 on success or an error code.
The following example deletes the scheduled job called “svr1_clean_stats”:
sp_sjobdrop ’svr1_clean_stats’
The following example deletes the schedule called “daily 01:00am”:
sp_sjobdrop ’sname=daily 01:00am’
The following example deletes the job called “load_sales_data”:
sp_sjobdrop ’jname=load_sales_data’
The following example deletes the job called “load_sales_data” and any scheduled jobs the caller owns that were using it.
sp_sjobdrop @name=’jname=load_sales_data’, @option=’all’
An error occurs if you attempt to delete a scheduled job that is currently running, unless you use the force option.
By default, the sp_sjobdrop procedure only operates on the scheduled job, job, or schedule specified by the @name argument.
For the general administration of jobs and schedules, the all and all_users options are provided. The following describes these options:
When a scheduled job is specified and the all or all_users option is not used, only the specified scheduled job is deleted.
When the all option is used for a scheduled job, the scheduled job will be deleted. If there are no more references to the job that will also be deleted, and if there are no more references to the schedule, then the schedule will also be deleted.
When a job or a schedule is specified and the all or all_users option is not used, an error occurs if the job or schedule is referenced by one or more scheduled jobs.
When the all_users option is used for a specified job or a schedule and the scheduled jobs that reference the job or schedule are owned by the caller, then the job or schedule and the referencing scheduled jobs are deleted.
When the all_users option is used for a specified job or a schedule and the caller has the js_admin_role, then the job or schedule and all referencing scheduled jobs are deleted.
The show option is provided so you can see which scheduled jobs, jobs, and schedules would be deleted by a call to sp_dropjob—without actually deleting them.
