Creates a new job executed by the SQL Server Agent service.
On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported. See Azure SQL Managed Instance T-SQL differences from SQL Server for details.
sp_add_job [ @job_name = ] N'job_name' [ , [ @enabled = ] enabled ] [ , [ @description = ] N'description' ] [ , [ @start_step_id = ] step_id ] [ , [ @category_name = ] 'category' ] [ , [ @category_id = ] category_id ] [ , [ @owner_login_name = ] 'login' ] [ , [ @notify_level_eventlog = ] eventlog_level ] [ , [ @notify_level_email = ] email_level ] [ , [ @notify_level_netsend = ] netsend_level ] [ , [ @notify_level_page = ] page_level ] [ , [ @notify_email_operator_name = ] 'email_name' ] [ , [ @notify_netsend_operator_name = ] 'netsend_name' ] [ , [ @notify_page_operator_name = ] 'page_name' ] [ , [ @delete_level = ] delete_level ] [ , [ @job_id = ] job_id OUTPUT ] [ ; ]
The name of the job. The name must be unique and can't contain the percent (
%) character. @job_name is nvarchar(128), with no default. Required.
Indicates the status of the added job. enabled is tinyint, with a default of
1 (enabled). If
0, the job isn't enabled and doesn't run according to its schedule; however, it can be run manually.
The description of the job. @description is nvarchar(512), with a default of
NULL. If @description is omitted,
N'No description available' is used.
The identification number of the first step to execute for the job. @start_step_id is int, with a default of
The category for the job. @category_name is sysname, with a default of
A language-independent mechanism for specifying a job category. @category_id is int, with a default of
The name of the login that owns the job. @owner_login_name is sysname, with a default of
NULL, which is interpreted as the current login name. Only members of the sysadmin fixed server role can set or change the value for @owner_login_name. If users who aren't members of the sysadmin role set or change the value of @owner_login_name, execution of this stored procedure fails and an error is returned.
A value indicating when to place an entry in the Microsoft Windows application log for this job. @notify_level_eventlog is int, and can be one of these values:
A value that indicates when to send an e-mail upon the completion of this job. @notify_level_email is int, with a default of
0, which indicates never. @notify_level_email uses the same values as @notify_level_eventlog.
A value that indicates when to send a network message upon the completion of this job. @notify_level_netsend is int, with a default of
0, which indicates never. @notify_level_netsend uses the same values as @notify_level_eventlog.
A value that indicates when to send a page upon the completion of this job. @notify_level_page is int, with a default of
0, which indicates never. @notify_level_page uses the same values as @notify_level_eventlog.
The e-mail name of the person to send e-mail to when @notify_email_operator_name is reached. @notify_email_operator_name is sysname, with a default of
The name of the operator to whom the network message is sent upon completion of this job. @notify_netsend_operator_name is sysname, with a default of
The name of the person to page upon completion of this job. @notify_page_operator_name is sysname, with a default of
A value that indicates when to delete the job. delete_value is int, with a default of
0, which means never. @delete_level uses the same values as @notify_level_eventlog.
When @delete_level is
3, the job is executed only once, regardless of any schedules defined for the job. Furthermore, if a job deletes itself, all history for the job is also deleted.
The job identification number assigned to the job if created successfully. @job_id is an output variable of type uniqueidentifier, with a default of
Return code values
0 (success) or
@originating_server exists in
sp_add_job, but isn't listed under Arguments. @originating_server is reserved for internal use.
sp_add_job has been executed to add a job,
sp_add_jobstep can be used to add steps that perform the activities for the job.
sp_add_jobschedule can be used to create the schedule that the SQL Server Agent service uses to execute the job.
sp_add_jobserver to set the SQL Server instance where the job executes, and
sp_delete_jobserver to remove the job from the SQL Server instance. If the job executes on one or more target servers in a multiserver environment, use
sp_apply_job_to_targets to set the target servers or target server groups for the job. To remove jobs from target servers or target server groups, use
sp_remove_job_from_targets. The Multi Server Administration (MSX/TSX) feature is not supported on Azure SQL Managed Instance.
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 shares the name of
sp_add_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_add_job (Azure Elastic Jobs) (Transact-SQL).
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
For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.
Only members of the sysadmin fixed server role can set or change the value for @owner_login_name. If users who aren't members of the sysadmin role set or change the value of @owner_login_name, execution of this stored procedure fails and an error is returned.
A. Add a job
This example adds a new job named
USE msdb; GO EXEC dbo.sp_add_job @job_name = N'NightlyBackups'; GO
B. Add a job with pager, e-mail, and net send information
This example creates a job named
Ad hoc Sales Data Backup that notifies
François Ajenstat (by pager, e-mail, or network pop-up message) if the job fails, and deletes the job upon successful completion.
This example assumes that an operator named
François Ajenstat and a login named
françoisa already exist.
USE msdb; GO EXEC dbo.sp_add_job @job_name = N'Ad hoc Sales Data Backup', @enabled = 1, @description = N'Ad hoc backup of sales data', @owner_login_name = N'françoisa', @notify_level_eventlog = 2, @notify_level_email = 2, @notify_level_netsend = 2, @notify_level_page = 2, @notify_email_operator_name = N'François Ajenstat', @notify_netsend_operator_name = N'François Ajenstat', @notify_page_operator_name = N'François Ajenstat', @delete_level = 1; GO
- sp_add_schedule (Transact-SQL)
- sp_add_jobstep (Transact-SQL)
- sp_add_jobserver (Transact-SQL)
- sp_apply_job_to_targets (Transact-SQL)
- sp_delete_job (Transact-SQL)
- sp_delete_jobserver (Transact-SQL)
- sp_remove_job_from_targets (Transact-SQL)
- sp_help_job (Transact-SQL)
- sp_help_jobstep (Transact-SQL)
- sp_update_job (Transact-SQL)