sp_stop_job (T-SQL)

Instructs SQL Server Agent to stop the execution of a job.

Syntax

sp_stop_job [@job_name =] 'job_name'
    | [@job_id =] job_id
    | [@originating_server =] 'master_server'
    | [@server_name =] 'target_server'

Arguments

[@job_name =] 'job_name'

Is the name of the job to stop. job_name is sysname, with a default of NULL.

[@job_id =] job_id

Is the identification number of the job to stop. job_id is uniqueidentifier, with a default of NULL.

[@originating_server =] 'master_server'

Is the name of the master server. If specified, all multiserver jobs are stopped. master_server is nvarchar(30), with a default of NULL. Specify this parameter only when calling sp_stop_job at a target server.

Note Only one of the first three parameters can be specified.

[@server_name =] 'target_server'

Is the name of the specific target server on which to stop a multiserver job. target_server is nvarchar(30), with a default of NULL. Specify this parameter only when calling sp_stop_job at a master server for a multiserver job.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

If a job is currently executing a step of type CmdExec, 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.

Permissions

Execute permissions default to the public role.

Examples

This example stops a job named Archive Tables.

USE msdb

EXEC sp_stop_job @job_name = 'Archive Tables'

See Also