Instructs SQL Server Agent to stop the execution of a job.
Applies to: SQL Server (SQL Server 2008 through current version).
When SQL Server Agent receives the stop notification, it waits for the job step that is currently running to finish before it processes the cancel request. Some long-running Transact-SQL statements such as BACKUP, RESTORE, and some DBCC commands can take a long time to finish. When these are running, it may take awhile before the job is canceled. Stopping a job causes a "Job Canceled" entry to be recorded in the job history.
If a job is currently executing a step of type CmdExec or PowerShell, the process being run (for example, MyProgram.exe) is forced to end prematurely. Premature ending can result in unpredictable behavior such as files in use by the process being held open. Consequently, sp_stop_job should be used only in extreme circumstances if the job contains steps of type CmdExec or PowerShell.
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:
For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.
Members of SQLAgentUserRole and SQLAgentReaderRole can only stop jobs that they own. Members of SQLAgentOperatorRole can stop all local jobs including those that are owned by other users. Members of sysadmin can stop all local and multiserver jobs.
The following example stops a job named Weekly Sales Data Backup.
USE msdb ; GO EXEC dbo.sp_stop_job N'Weekly Sales Data Backup' ; GO