使用 Transact-SQL (T-SQL) 创建和管理弹性数据库作业Use Transact-SQL (T-SQL) to create and manage Elastic Database Jobs

本文通过许多示例方案说明了如何使用 T-SQL 来完成弹性作业的入门。This article provides many example scenarios to get started working with Elastic Jobs using T-SQL.

这些示例使用 作业数据库中提供的存储过程视图The examples use the stored procedures and views available in the job database.

Transact-SQL (T-SQL) 用于创建、配置、执行和管理作业。Transact-SQL (T-SQL) is used to create, configure, execute, and manage jobs. T-SQL 不支持创建弹性作业代理,因此必须先使用门户或 PowerShell 创建弹性作业代理 。Creating the Elastic Job agent is not supported in T-SQL, so you must first create an Elastic Job agent using the portal, or PowerShell.

创建执行作业所需的凭据Create a credential for job execution

此凭据用于连接到目标数据库,以便执行脚本。The credential is used to connect to your target databases for script execution. 此凭据需要在目标组指定的数据库上拥有适当的权限,否则无法成功地执行脚本。The credential needs appropriate permissions, on the databases specified by the target group, to successfully execute the script. 使用服务器和/或池目标组成员时,强烈建议创建一个用于刷新此凭据的主凭据,然后再在执行作业时扩展服务器和/或池。When using a server and/or pool target group member, it is highly suggested to create a master credential for use to refresh the credential prior to expansion of the server and/or pool at time of job execution. 数据库范围的凭据在作业代理数据库中创建。The database scoped credential is created on the job agent database. 必须使用同一凭据来创建登录名,并创建基于登录名的用户,以便在目标数据库上授予登录数据库权限。 The same credential must be used to Create a Login and Create a User from Login to grant the Login Database Permissions on the target databases.

--Connect to the job database specified when creating the job agent

-- Create a db master key if one does not already exist, using your own password.  
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<EnterStrongPasswordHere>';  
  
-- Create a database scoped credential.  
CREATE DATABASE SCOPED CREDENTIAL myjobcred WITH IDENTITY = 'jobcred',
    SECRET = '<EnterStrongPasswordHere>'; 
GO

-- Create a database scoped credential for the master database of server1.
CREATE DATABASE SCOPED CREDENTIAL mymastercred WITH IDENTITY = 'mastercred',
    SECRET = '<EnterStrongPasswordHere>'; 
GO

创建目标组(服务器)Create a target group (servers)

以下示例演示如何针对服务器中的所有数据库执行作业。The following example shows how to execute a job against all databases in a server.
连接到 作业数据库,然后运行以下命令:Connect to the job database and run the following command:

-- Connect to the job database specified when creating the job agent

-- Add a target group containing server(s)
EXEC jobs.sp_add_target_group 'ServerGroup1'

-- Add a server target member
EXEC jobs.sp_add_target_group_member
'ServerGroup1',
@target_type = 'SqlServer',
@refresh_credential_name='mymastercred', --credential required to refresh the databases in server
@server_name='server1.database.chinacloudapi.cn'

--View the recently created target group and target group members
SELECT * FROM jobs.target_groups WHERE target_group_name='ServerGroup1';
SELECT * FROM jobs.target_group_members WHERE target_group_name='ServerGroup1';

排除单个数据库Exclude an individual database

以下示例演示如何针对 SQL 数据库服务器中的所有数据库执行作业,名为 MappingDB 的数据库除外。The following example shows how to execute a job against all databases in a SQL Database server, except for the database named MappingDB.
连接到 作业数据库,然后运行以下命令:Connect to the job database and run the following command:

--Connect to the job database specified when creating the job agent

-- Add a target group containing server(s)
EXEC [jobs].sp_add_target_group N'ServerGroup'
GO

-- Add a server target member
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroup',
@target_type = N'SqlServer',
@refresh_credential_name=N'mymastercred', --credential required to refresh the databases in server
@server_name=N'London.database.chinacloudapi.cn'
GO

-- Add a server target member
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroup',
@target_type = N'SqlServer',
@refresh_credential_name=N'mymastercred', --credential required to refresh the databases in server
@server_name='server2.database.chinacloudapi.cn'
GO

--Exclude a database target member from the server target group
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroup',
@membership_type = N'Exclude',
@target_type = N'SqlDatabase',
@server_name = N'server1.database.chinacloudapi.cn',
@database_name =N'MappingDB'
GO

--View the recently created target group and target group members
SELECT * FROM [jobs].target_groups WHERE target_group_name = N'ServerGroup';
SELECT * FROM [jobs].target_group_members WHERE target_group_name = N'ServerGroup';

创建目标组(池)Create a target group (pools)

以下示例演示如何以一个或多个弹性池中的所有数据库为目标。The following example shows how to target all the databases in one or more elastic pools.
连接到 作业数据库,然后运行以下命令:Connect to the job database and run the following command:

--Connect to the job database specified when creating the job agent

-- Add a target group containing pool(s)
EXEC jobs.sp_add_target_group 'PoolGroup'

-- Add an elastic pool(s) target member
EXEC jobs.sp_add_target_group_member
'PoolGroup',
@target_type = 'SqlElasticPool',
@refresh_credential_name='mymastercred', --credential required to refresh the databases in server
@server_name='server1.database.chinacloudapi.cn',
@elastic_pool_name='ElasticPool-1'

-- View the recently created target group and target group members
SELECT * FROM jobs.target_groups WHERE target_group_name = N'PoolGroup';
SELECT * FROM jobs.target_group_members WHERE target_group_name = N'PoolGroup';

将新架构部署到多个数据库Deploy new schema to many databases

以下示例演示如何将新架构部署到所有数据库。The following example shows how to deploy new schema to all databases.
连接到 作业数据库,然后运行以下命令:Connect to the job database and run the following command:

--Connect to the job database specified when creating the job agent

--Add job for create table
EXEC jobs.sp_add_job @job_name='CreateTableTest', @description='Create Table Test'

-- Add job step for create table
EXEC jobs.sp_add_jobstep @job_name='CreateTableTest',
@command=N'IF NOT EXISTS (SELECT * FROM sys.tables 
            WHERE object_id = object_id(''Test''))
CREATE TABLE [dbo].[Test]([TestId] [int] NOT NULL);',
@credential_name='myjobcred',
@target_group_name='PoolGroup'

使用内置参数进行数据收集Data collection using built-in parameters

在许多数据收集方案中,可以包括部分下述脚本变量,以便对作业结果进行后期处理。In many data collection scenarios, it can be useful to include some of these scripting variables to help post-process the results of the job.

  • $(job_name)$(job_name)
  • $(job_id)$(job_id)
  • $(job_version)$(job_version)
  • $(step_id)$(step_id)
  • $(step_name)$(step_name)
  • $(job_execution_id)$(job_execution_id)
  • $(job_execution_create_time)$(job_execution_create_time)
  • $(target_group_name)$(target_group_name)

例如,若要将同一作业执行操作的所有结果组合到一起,请使用 $(job_execution_id) ,如以下命令所示:For example, to group all results from the same job execution together, use the $(job_execution_id) as shown in the following command:

@command= N' SELECT DB_NAME() DatabaseName, $(job_execution_id) AS job_execution_id, * FROM sys.dm_db_resource_stats WHERE end_time > DATEADD(mi, -20, GETDATE());'

监视数据库性能Monitor database performance

以下示例创建一个新作业,以便从多个数据库收集性能数据。The following example creates a new job to collect performance data from multiple databases.

默认情况下,作业代理将查找创建表以存储返回的结果。By default the job agent will look to create the table to store the returned results in. 因此,与用于输出凭据的凭据相关联的登录将需要具有足够的权限来执行此操作。As a result the login associated with the credential used for the output credential will need to have sufficient permissions to perform this. 如果要提前手动创建表,则需要具有以下属性:If you want to manually create the table ahead of time then it needs to have the following properties:

  1. 具有结果集的正确名称和数据类型的列。Columns with the correct name and data types for the result set.
  2. 数据类型为 uniqueidentifier 的 internal_execution_id 的其他列。Additional column for internal_execution_id with the data type of uniqueidentifier.
  3. internal_execution_id 列上名为 IX_<TableName>_Internal_Execution_ID 的非聚集索引。A nonclustered index named IX_<TableName>_Internal_Execution_ID on the internal_execution_id column.

连接到 作业数据库,然后运行以下命令:Connect to the job database and run the following commands:

--Connect to the job database specified when creating the job agent

-- Add a job to collect perf results
EXEC jobs.sp_add_job @job_name ='ResultsJob', @description='Collection Performance data from all customers'

-- Add a job step w/ schedule to collect results
EXEC jobs.sp_add_jobstep
@job_name='ResultsJob',
@command= N' SELECT DB_NAME() DatabaseName, $(job_execution_id) AS job_execution_id, * FROM sys.dm_db_resource_stats WHERE end_time > DATEADD(mi, -20, GETDATE());',
@credential_name='myjobcred',
@target_group_name='PoolGroup',
@output_type='SqlDatabase',
@output_credential_name='myjobcred',
@output_server_name='server1.database.chinacloudapi.cn',
@output_database_name='<resultsdb>',
@output_table_name='<resutlstable>'
Create a job to monitor pool performance
--Connect to the job database specified when creating the job agent

-- Add a target group containing master database
EXEC jobs.sp_add_target_group 'MasterGroup'

-- Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name='MasterGroup',
@target_type='SqlDatabase',
@server_name='server1.database.chinacloudapi.cn',
@database_name='master'

-- Add a job to collect perf results
EXEC jobs.sp_add_job
@job_name='ResultsPoolsJob',
@description='Demo: Collection Performance data from all pools',
@schedule_interval_type='Minutes',
@schedule_interval_count=15

-- Add a job step w/ schedule to collect results
EXEC jobs.sp_add_jobstep
@job_name='ResultsPoolsJob',
@command=N'declare @now datetime
DECLARE @startTime datetime
DECLARE @endTime datetime
DECLARE @poolLagMinutes datetime
DECLARE @poolStartTime datetime
DECLARE @poolEndTime datetime
SELECT @now = getutcdate ()
SELECT @startTime = dateadd(minute, -15, @now)
SELECT @endTime = @now
SELECT @poolStartTime = dateadd(minute, -30, @startTime)
SELECT @poolEndTime = dateadd(minute, -30, @endTime)

SELECT elastic_pool_name , end_time, elastic_pool_dtu_limit, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, max_worker_percent, max_session_percent,
        avg_storage_percent, elastic_pool_storage_limit_mb FROM sys.elastic_pool_resource_stats
        WHERE end_time > @poolStartTime and end_time <= @poolEndTime;
',
@credential_name='myjobcred',
@target_group_name='MasterGroup',
@output_type='SqlDatabase',
@output_credential_name='myjobcred',
@output_server_name='server1.database.chinacloudapi.cn',
@output_database_name='resultsdb',
@output_table_name='resutlstable'

查看作业定义View job definitions

以下示例演示了如何查看当前的作业定义。The following example shows how to view current job definitions.
连接到 作业数据库,然后运行以下命令:Connect to the job database and run the following command:

--Connect to the job database specified when creating the job agent

-- View all jobs
SELECT * FROM jobs.jobs

-- View the steps of the current version of all jobs
SELECT js.* FROM jobs.jobsteps js
JOIN jobs.jobs j 
  ON j.job_id = js.job_id AND j.job_version = js.job_version

-- View the steps of all versions of all jobs
select * from jobs.jobsteps

开始即席执行作业Begin ad hoc execution of a job

以下示例演示如何立即启动作业。The following example shows how to start a job immediately.
连接到 作业数据库,然后运行以下命令:Connect to the job database and run the following command:

--Connect to the job database specified when creating the job agent

-- Execute the latest version of a job
EXEC jobs.sp_start_job 'CreateTableTest'

-- Execute the latest version of a job and receive the execution id
declare @je uniqueidentifier
exec jobs.sp_start_job 'CreateTableTest', @job_execution_id = @je output
select @je

select * from jobs.job_executions where job_execution_id = @je

-- Execute a specific version of a job (e.g. version 1)
exec jobs.sp_start_job 'CreateTableTest', 1

计划作业的执行Schedule execution of a job

以下示例演示如何计划一项将来执行的作业。The following example shows how to schedule a job for future execution.
连接到 作业数据库,然后运行以下命令:Connect to the job database and run the following command:

--Connect to the job database specified when creating the job agent

EXEC jobs.sp_update_job
@job_name='ResultsJob',
@enabled=1,
@schedule_interval_type='Minutes',
@schedule_interval_count=15

监视作业执行状态Monitor job execution status

以下示例演示如何查看所有作业的执行状态详细信息。The following example shows how to view execution status details for all jobs.
连接到 作业数据库,然后运行以下命令:Connect to the job database and run the following command:

--Connect to the job database specified when creating the job agent

--View top-level execution status for the job named 'ResultsPoolJob'
SELECT * FROM jobs.job_executions 
WHERE job_name = 'ResultsPoolsJob' and step_id IS NULL
ORDER BY start_time DESC

--View all top-level execution status for all jobs
SELECT * FROM jobs.job_executions WHERE step_id IS NULL
ORDER BY start_time DESC

--View all execution statuses for job named 'ResultsPoolsJob'
SELECT * FROM jobs.job_executions 
WHERE job_name = 'ResultsPoolsJob' 
ORDER BY start_time DESC

-- View all active executions
SELECT * FROM jobs.job_executions 
WHERE is_active = 1
ORDER BY start_time DESC

取消作业Cancel a job

以下示例演示如何取消作业。The following example shows how to cancel a job.
连接到 作业数据库,然后运行以下命令:Connect to the job database and run the following command:

--Connect to the job database specified when creating the job agent

-- View all active executions to determine job execution id
SELECT * FROM jobs.job_executions 
WHERE is_active = 1 AND job_name = 'ResultPoolsJob'
ORDER BY start_time DESC
GO

-- Cancel job execution with the specified job execution id
EXEC jobs.sp_stop_job '01234567-89ab-cdef-0123-456789abcdef'

删除旧的作业历史记录Delete old job history

以下示例演示如何删除特定日期之前的作业历史记录。The following example shows how to delete job history prior to a specific date.
连接到 作业数据库,然后运行以下命令:Connect to the job database and run the following command:

--Connect to the job database specified when creating the job agent

-- Delete history of a specific job's executions older than the specified date
EXEC jobs.sp_purge_jobhistory @job_name='ResultPoolsJob', @oldest_date='2016-07-01 00:00:00'

--Note: job history is automatically deleted if it is >45 days old

删除作业及其所有历史记录Delete a job and all its job history

以下示例演示如何删除作业以及所有相关的作业历史记录。The following example shows how to delete a job and all related job history.
连接到 作业数据库,然后运行以下命令:Connect to the job database and run the following command:

--Connect to the job database specified when creating the job agent

EXEC jobs.sp_delete_job @job_name='ResultsPoolsJob'

--Note: job history is automatically deleted if it is >45 days old

作业存储过程Job stored procedures

以下存储过程位于作业数据库中。The following stored procedures are in the jobs database.

存储过程Stored procedure 说明Description
sp_add_jobsp_add_job 添加新的作业。Adds a new job.
sp_update_jobsp_update_job 更新现有的作业。Updates an existing job.
sp_delete_jobsp_delete_job 删除现有的作业。Deletes an existing job.
sp_add_jobstepsp_add_jobstep 向作业添加步骤。Adds a step to a job.
sp_update_jobstepsp_update_jobstep 更新作业步骤。Updates a job step.
sp_delete_jobstepsp_delete_jobstep 删除作业步骤。Deletes a job step.
sp_start_jobsp_start_job 开始执行作业。Starts executing a job.
sp_stop_jobsp_stop_job 停止作业的执行。Stops a job execution.
sp_add_target_groupsp_add_target_group 添加目标组。Adds a target group.
sp_delete_target_groupsp_delete_target_group 删除目标组。Deletes a target group.
sp_add_target_group_membersp_add_target_group_member 向目标组添加一个或一组数据库。Adds a database or group of databases to a target group.
sp_delete_target_group_membersp_delete_target_group_member 从目标组中删除目标组成员。Removes a target group member from a target group.
sp_purge_jobhistorysp_purge_jobhistory 删除作业的历史记录。Removes the history records for a job.

sp_add_jobsp_add_job

添加新的作业。Adds a new job.

语法Syntax

[jobs].sp_add_job [ @job_name = ] 'job_name'  
    [ , [ @description = ] 'description' ]   
    [ , [ @enabled = ] enabled ]
    [ , [ @schedule_interval_type = ] schedule_interval_type ]  
    [ , [ @schedule_interval_count = ] schedule_interval_count ]   
    [ , [ @schedule_start_time = ] schedule_start_time ]   
    [ , [ @schedule_end_time = ] schedule_end_time ]   
    [ , [ @job_id = ] job_id OUTPUT ]

参数Arguments

[ @job_name = ] 'job_name'[ @job_name = ] 'job_name'
作业的名称。The name of the job. 名称必须唯一,不能包含百分比 (%) 字符。The name must be unique and cannot contain the percent (%) character. job_name 为 nvarchar(128),没有默认值。job_name is nvarchar(128), with no default.

[ @description = ] 'description'[ @description = ] 'description'
作业的说明。The description of the job. description 为 nvarchar(512),默认值为 NULL。description is nvarchar(512), with a default of NULL. 如果省略 description,则使用空字符串。If description is omitted, an empty string is used.

[ @enabled = ] enabled[ @enabled = ] enabled
作业的计划是否已启用。Whether the job's schedule is enabled. enabled 为 bit,默认值为 0(禁用)。Enabled is bit, with a default of 0 (disabled). 如果为 0,则作业未启用,不会按计划运行,但可手动运行。If 0, the job is not enabled and does not run according to its schedule; however, it can be run manually. 如果为 1,则作业会按计划运行,也可手动运行。If 1, the job will run according to its schedule, and can also be run manually.

[ @schedule_interval_type = ] schedule_interval_type[ @schedule_interval_type =] schedule_interval_type
其值指示何时会执行作业。Value indicates when the job is to be executed. schedule_interval_type 为 nvarchar(50),默认值为 Once,可以是下述值之一:schedule_interval_type is nvarchar(50), with a default of Once, and can be one of the following values:

  • 'Once';'Once',
  • 'Minutes';'Minutes',
  • 'Hours';'Hours',
  • 'Days';'Days',
  • 'Weeks';'Weeks',
  • 'Months''Months'

[ @schedule_interval_count = ] schedule_interval_count[ @schedule_interval_count = ] schedule_interval_count
每次执行作业时,其间会出现的 schedule_interval_count 期间数。Number of schedule_interval_count periods to occur between each execution of the job. schedule_interval_count 为 int,默认值为 1。schedule_interval_count is int, with a default of 1. 该值必须大于或等于 1。The value must be greater than or equal to 1.

[ @schedule_start_time = ] schedule_start_time[ @schedule_start_time = ] schedule_start_time
可以开始执行作业的日期。Date on which job execution can begin. schedule_start_time 为 DATETIME2,默认值为 0001-01-01 00:00:00.0000000。schedule_start_time is DATETIME2, with the default of 0001-01-01 00:00:00.0000000.

[ @schedule_end_time = ] schedule_end_time[ @schedule_end_time = ] schedule_end_time
可以停止执行作业的日期。Date on which job execution can stop. schedule_end_time 为 DATETIME2,默认值为 9999-12-31 11:59:59.0000000。schedule_end_time is DATETIME2, with the default of 9999-12-31 11:59:59.0000000.

[ @job_id = ] job_id OUTPUT[ @job_id = ] job_id OUTPUT
分配给已成功创建的作业的作业标识号。The job identification number assigned to the job if created successfully. job_id 是类型为 uniqueidentifier 的输出变量。job_id is an output variable of type uniqueidentifier.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

sp_add_job 必须从创建作业代理时指定的作业代理数据库运行。sp_add_job must be run from the job agent database specified when creating the job agent. 通过执行 sp_add_job 来添加作业以后,即可使用 sp_add_jobstep 来添加那些为作业执行活动的步骤。After 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. 作业的初始版本号为 0,在添加第一个步骤后会递增到 1。The job's initial version number is 0, which will be incremented to 1 when the first step is added.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性。Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

sp_update_jobsp_update_job

更新现有的作业。Updates an existing job.

语法Syntax

[jobs].sp_update_job [ @job_name = ] 'job_name'  
    [ , [ @new_name = ] 'new_name' ]
    [ , [ @description = ] 'description' ]   
    [ , [ @enabled = ] enabled ]
    [ , [ @schedule_interval_type = ] schedule_interval_type ]  
    [ , [ @schedule_interval_count = ] schedule_interval_count ]   
    [ , [ @schedule_start_time = ] schedule_start_time ]   
    [ , [ @schedule_end_time = ] schedule_end_time ]   

参数Arguments

[ @job_name = ] 'job_name'[ @job_name = ] 'job_name'
要更新的作业的名称。The name of the job to be updated. job_name 为 nvarchar(128)。job_name is nvarchar(128).

[ @new_name = ] 'new_name'[ @new_name = ] 'new_name'
作业的新名称。The new name of the job. new_name 为 nvarchar(128)。new_name is nvarchar(128).

[ @description = ] 'description'[ @description = ] 'description'
作业的说明。The description of the job. description 为 nvarchar(512)。description is nvarchar(512).

[ @enabled = ] enabled[ @enabled = ] enabled
指定作业的计划是已启用 (1) 还是未启用 (0)。Specifies whether the job's schedule is enabled (1) or not enabled (0). enabled 为 bit。Enabled is bit.

[ @schedule_interval_type= ] schedule_interval_type[ @schedule_interval_type= ] schedule_interval_type
其值指示何时会执行作业。Value indicates when the job is to be executed. schedule_interval_type 为 nvarchar(50),可以是下述值之一:schedule_interval_type is nvarchar(50) and can be one of the following values:

  • 'Once';'Once',
  • 'Minutes';'Minutes',
  • 'Hours';'Hours',
  • 'Days';'Days',
  • 'Weeks';'Weeks',
  • 'Months''Months'

[ @schedule_interval_count= ] schedule_interval_count[ @schedule_interval_count= ] schedule_interval_count
每次执行作业时,其间会出现的 schedule_interval_count 期间数。Number of schedule_interval_count periods to occur between each execution of the job. schedule_interval_count 为 int,默认值为 1。schedule_interval_count is int, with a default of 1. 该值必须大于或等于 1。The value must be greater than or equal to 1.

[ @schedule_start_time= ] schedule_start_time[ @schedule_start_time= ] schedule_start_time
可以开始执行作业的日期。Date on which job execution can begin. schedule_start_time 为 DATETIME2,默认值为 0001-01-01 00:00:00.0000000。schedule_start_time is DATETIME2, with the default of 0001-01-01 00:00:00.0000000.

[ @schedule_end_time= ] schedule_end_time[ @schedule_end_time= ] schedule_end_time
可以停止执行作业的日期。Date on which job execution can stop. schedule_end_time 为 DATETIME2,默认值为 9999-12-31 11:59:59.0000000。schedule_end_time is DATETIME2, with the default of 9999-12-31 11:59:59.0000000.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

通过执行 sp_add_job 来添加作业以后,即可使用 sp_add_jobstep 来添加那些为作业执行活动的步骤。After 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. 作业的初始版本号为 0,在添加第一个步骤后会递增到 1。The job's initial version number is 0, which will be incremented to 1 when the first step is added.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性。Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

sp_delete_jobsp_delete_job

删除现有的作业。Deletes an existing job.

语法Syntax

[jobs].sp_delete_job [ @job_name = ] 'job_name'
    [ , [ @force = ] force ]

参数Arguments

[ @job_name = ] 'job_name'[ @job_name = ] 'job_name'
要删除的作业的名称。The name of the job to be deleted. job_name 为 nvarchar(128)。job_name is nvarchar(128).

[ @force = ] force[ @force = ] force
指定是在作业有正在进行的执行操作的情况下删除作业并取消所有正在进行的执行操作 (1),还是在有正在进行的作业执行操作的情况下让作业失败 (0)。Specifies whether to delete if the job has any executions in progress and cancel all in-progress executions (1) or fail if any job executions are in progress (0). force 为 bit。force is bit.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

删除作业时,会自动删除作业历史记录。Job history is automatically deleted when a job is deleted.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性。Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

sp_add_jobstepsp_add_jobstep

向作业添加步骤。Adds a step to a job.

语法Syntax

[jobs].sp_add_jobstep [ @job_name = ] 'job_name'   
     [ , [ @step_id = ] step_id ]   
     [ , [ @step_name = ] step_name ]   
     [ , [ @command_type = ] 'command_type' ]   
     [ , [ @command_source = ] 'command_source' ]  
     , [ @command = ] 'command'
     , [ @credential_name = ] 'credential_name'
     , [ @target_group_name = ] 'target_group_name'
     [ , [ @initial_retry_interval_seconds = ] initial_retry_interval_seconds ]   
     [ , [ @maximum_retry_interval_seconds = ] maximum_retry_interval_seconds ]   
     [ , [ @retry_interval_backoff_multiplier = ] retry_interval_backoff_multiplier ]   
     [ , [ @retry_attempts = ] retry_attempts ]   
     [ , [ @step_timeout_seconds = ] step_timeout_seconds ]   
     [ , [ @output_type = ] 'output_type' ]   
     [ , [ @output_credential_name = ] 'output_credential_name' ]   
     [ , [ @output_subscription_id = ] 'output_subscription_id' ]   
     [ , [ @output_resource_group_name = ] 'output_resource_group_name' ]   
     [ , [ @output_server_name = ] 'output_server_name' ]   
     [ , [ @output_database_name = ] 'output_database_name' ]   
     [ , [ @output_schema_name = ] 'output_schema_name' ]   
     [ , [ @output_table_name = ] 'output_table_name' ]
     [ , [ @job_version = ] job_version OUTPUT ]
     [ , [ @max_parallelism = ] max_parallelism ]

参数Arguments

[ @job_name = ] 'job_name'[ @job_name = ] 'job_name'
要向其添加步骤的作业的名称。The name of the job to which to add the step. job_name 为 nvarchar(128)。job_name is nvarchar(128).

[ @step_id = ] step_id[ @step_id = ] step_id
作业步骤的序列标识号。The sequence identification number for the job step. 步骤标识号从 1 开始连续递增。Step identification numbers start at 1 and increment without gaps. 如果某个现有的步骤已经有此 ID,则该步骤以及所有后续步骤的 ID 都会递增,于是这个新步骤就可以插入到序列中。If an existing step already has this id, then that step and all following steps will have their id's incremented so that this new step can be inserted into the sequence. 在未指定的情况下,step_id 会自动分配给步骤序列中的最后一个项。If not specified, the step_id will be automatically assigned to the last in the sequence of steps. step_id 为 int。step_id is an int.

[ @step_name = ] step_name[ @step_name = ] step_name
步骤的名称。The name of the step. 必须指定,除非是作业的第一个步骤,(为方便起见),该步骤的默认名称为“JobStep”。Must be specified, except for the first step of a job which (for convenience) has a default name of 'JobStep'. step_name 为 nvarchar(128)。step_name is nvarchar(128).

[ @command_type = ] 'command_type'[ @command_type = ] 'command_type'
由此 jobstep 执行的命令的类型。The type of command that is executed by this jobstep. command_type 为 nvarchar(50),默认值为 TSql,这意味着 @command_type 参数的值为 T-SQL 脚本。command_type is nvarchar(50), with a default value of TSql, meaning that the value of the @command_type parameter is a T-SQL script.

如果指定,此值必须为 TSql。If specified, the value must be TSql.

[ @command_source = ] 'command_source'[ @command_source = ] 'command_source'
命令存储位置的类型。The type of location where the command is stored. command_source 为 nvarchar(50),默认值为 Inline,这意味着 @command_source 参数的值为命令的文字文本。command_source is nvarchar(50), with a default value of Inline, meaning that the value of the @command_source parameter is the literal text of the command.

如果指定,此值必须为 Inline。If specified, the value must be Inline.

[ @command = ] 'command'[ @command = ] 'command'
命令必须是有效的 T-SQL 脚本,然后才能通过此作业步骤执行该命令。The command must be valid T-SQL script and is then executed by this job step. command 为 nvarchar(max),默认值为 NULL。command is nvarchar(max), with a default of NULL.

[ @credential_name = ] 'credential_name'[ @credential_name = ] 'credential_name'
存储在此作业控制数据库中的数据库范围的凭据的名称,此控制数据库用于在执行此步骤时连接到目标组中的每个目标数据库。The name of the database scoped credential stored in this job control database that is used to connect to each of the target databases within the target group when this step is executed. credential_name 为 nvarchar(128)。credential_name is nvarchar(128).

[ @target_group_name = ] 'target-group_name'[ @target_group_name = ] 'target-group_name'
包含目标数据库(作业步骤将在其上执行)的目标组的名称。The name of the target group that contains the target databases that the job step will be executed on. target_group_name 为 nvarchar(128)。target_group_name is nvarchar(128).

[ @initial_retry_interval_seconds = ] initial_retry_interval_seconds[ @initial_retry_interval_seconds = ] initial_retry_interval_seconds
第一次重试尝试之前的延迟,前提是作业步骤在初始执行尝试时失败。The delay before the first retry attempt, if the job step fails on the initial execution attempt. initial_retry_interval_seconds 为 int,默认值为 1。initial_retry_interval_seconds is int, with default value of 1.

[ @maximum_retry_interval_seconds = ] maximum_retry_interval_seconds[ @maximum_retry_interval_seconds = ] maximum_retry_interval_seconds
重试尝试之间的最大延迟。The maximum delay between retry attempts. 如果重试之间的延迟大于此值,则会将其削减到此值。If the delay between retries would grow larger than this value, it is capped to this value instead. maximum_retry_interval_seconds 为 int,默认值为 120。maximum_retry_interval_seconds is int, with default value of 120.

[ @retry_interval_backoff_multiplier = ] retry_interval_backoff_multiplier[ @retry_interval_backoff_multiplier = ] retry_interval_backoff_multiplier
将要应用到重试延迟的乘数,前提是多个作业步骤执行尝试失败。The multiplier to apply to the retry delay if multiple job step execution attempts fail. 例如,如果首次重试的延迟为 5 秒,而回退乘数为 2.0,则第二次重试的延迟为 10 秒,第三次重试的延迟为 20 秒。For example, if the first retry had a delay of 5 second and the backoff multiplier is 2.0, then the second retry will have a delay of 10 seconds and the third retry will have a delay of 20 seconds. retry_interval_backoff_multiplier 为 real,默认值为 2.0。retry_interval_backoff_multiplier is real, with default value of 2.0.

[ @retry_attempts = ] retry_attempts[ @retry_attempts = ] retry_attempts
重试执行的次数,前提是初始尝试失败。The number of times to retry execution if the initial attempt fails. 例如,如果 retry_attempts 值为 10,则初始尝试次数为 1,重试尝试次数为 10,总尝试次数为 11。For example, if the retry_attempts value is 10, then there will be 1 initial attempt and 10 retry attempts, giving a total of 11 attempts. 如果最后的重试尝试失败,则作业执行操作会终止,生命周期状态为“失败”。If the final retry attempt fails, then the job execution will terminate with a lifecycle of Failed. retry_attempts 为 int,默认值为 10。retry_attempts is int, with default value of 10.

[ @step_timeout_seconds = ] step_timeout_seconds[ @step_timeout_seconds = ] step_timeout_seconds
允许步骤执行的最长时间。The maximum amount of time allowed for the step to execute. 如果超出此时间,则作业执行操作会终止,生命周期状态为“超时”。If this time is exceeded, then the job execution will terminate with a lifecycle of TimedOut. step_timeout_seconds 为 int,默认值为 43,200 秒(12 小时)。step_timeout_seconds is int, with default value of 43,200 seconds (12 hours).

[ @output_type = ] 'output_type'[ @output_type = ] 'output_type'
如果不为 null,则为将命令的首个结果集写入到其中的目标的类型。If not null, the type of destination that the command's first result set is written to. output_type 为 nvarchar(50),默认值为 NULL。output_type is nvarchar(50), with a default of NULL.

如果指定,此值必须为 SqlDatabase。If specified, the value must be SqlDatabase.

[ @output_credential_name = ] 'output_credential_name'[ @output_credential_name = ] 'output_credential_name'
如果不为 null,则为数据库范围的凭据的名称,该凭据用于连接到输出目标数据库。If not null, the name of the database scoped credential that is used to connect to the output destination database. 如果 output_type 等于 SqlDatabase,则必须指定此项。Must be specified if output_type equals SqlDatabase. output_credential_name 为 nvarchar(128),默认值为 NULL。output_credential_name is nvarchar(128), with a default value of NULL.

[ @output_subscription_id = ] 'output_subscription_id'[ @output_subscription_id = ] 'output_subscription_id'
需要描述。Needs description.

[ @output_resource_group_name = ] 'output_resource_group_name'[ @output_resource_group_name = ] 'output_resource_group_name'
需要描述。Needs description.

[ @output_server_name = ] 'output_server_name'[ @output_server_name = ] 'output_server_name'
如果不为 null,则为服务器的完全限定的 DNS 名称,该服务器包含输出目标数据库。If not null, the fully qualified DNS name of the server that contains the output destination database. 如果 output_type 等于 SqlDatabase,则必须指定此项。Must be specified if output_type equals SqlDatabase. output_server_name 为 nvarchar(256),默认值为 NULL。output_server_name is nvarchar(256), with a default of NULL.

[ @output_database_name = ] 'output_database_name'[ @output_database_name = ] 'output_database_name'
如果不为 null,则为包含输出目标表的数据库的名称。If not null, the name of the database that contains the output destination table. 如果 output_type 等于 SqlDatabase,则必须指定此项。Must be specified if output_type equals SqlDatabase. output_database_name 为 nvarchar(128),默认值为 NULL。output_database_name is nvarchar(128), with a default of NULL.

[ @output_schema_name = ] 'output_schema_name'[ @output_schema_name = ] 'output_schema_name'
如果不为 null,则为包含输出目标表的 SQL 架构的名称。If not null, the name of the SQL schema that contains the output destination table. 如果 output_type 等于 SqlDatabase,则默认值为 dbo。If output_type equals SqlDatabase, the default value is dbo. output_schema_name 为 nvarchar(128)。output_schema_name is nvarchar(128).

[ @output_table_name = ] 'output_table_name'[ @output_table_name = ] 'output_table_name'
如果不为 null,则为将命令的首个结果集写入到其中的表的名称。If not null, the name of the table that the command's first result set will be written to. 如果此表不存在,则会根据返回结果集的架构来创建它。If the table doesn't already exist, it will be created based on the schema of the returning result-set. 如果 output_type 等于 SqlDatabase,则必须指定此项。Must be specified if output_type equals SqlDatabase. output_table_name 为 nvarchar(128),默认值为 NULL。output_table_name is nvarchar(128), with a default value of NULL.

[ @job_version = ] job_version OUTPUT[ @job_version = ] job_version OUTPUT
一个输出参数,将会为其分配新作业版本号。Output parameter that will be assigned the new job version number. job_version 为 int。job_version is int.

[ @max_parallelism = ] max_parallelism OUTPUT[ @max_parallelism = ] max_parallelism OUTPUT
每个弹性池的最大并行度级别。The maximum level of parallelism per elastic pool. 如果设置此项,则作业步骤会受到限制,每个弹性池中可以在其上运行作业步骤的数据库会有一个最大数目。If set, then the job step will be restricted to only run on a maximum of that many databases per elastic pool. 这适用于每个特定的弹性池,该弹性池直接包括在目标组中,或者位于一个服务器中,而该服务器则包括在目标组中。This applies to each elastic pool that is either directly included in the target group or is inside a server that is included in the target group. max_parallelism 为 int。max_parallelism is int.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

当 sp_add_jobstep 成功时,作业的当前版本号会递增。When sp_add_jobstep succeeds, the job's current version number is incremented. 下次执行作业时,会使用新版本。The next time the job is executed, the new version will be used. 如果作业目前正在执行,该执行不会包含新步骤。If the job is currently executing, that execution will not contain the new step.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性。Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

sp_update_jobstepsp_update_jobstep

更新作业步骤。Updates a job step.

语法Syntax

[jobs].sp_update_jobstep [ @job_name = ] 'job_name'   
     [ , [ @step_id = ] step_id ]   
     [ , [ @step_name = ] 'step_name' ]   
     [ , [ @new_id = ] new_id ]   
     [ , [ @new_name = ] 'new_name' ]   
     [ , [ @command_type = ] 'command_type' ]   
     [ , [ @command_source = ] 'command_source' ]  
     , [ @command = ] 'command'
     , [ @credential_name = ] 'credential_name'
     , [ @target_group_name = ] 'target_group_name'
     [ , [ @initial_retry_interval_seconds = ] initial_retry_interval_seconds ]   
     [ , [ @maximum_retry_interval_seconds = ] maximum_retry_interval_seconds ]   
     [ , [ @retry_interval_backoff_multiplier = ] retry_interval_backoff_multiplier ]   
     [ , [ @retry_attempts = ] retry_attempts ]   
     [ , [ @step_timeout_seconds = ] step_timeout_seconds ]   
     [ , [ @output_type = ] 'output_type' ]   
     [ , [ @output_credential_name = ] 'output_credential_name' ]   
     [ , [ @output_server_name = ] 'output_server_name' ]   
     [ , [ @output_database_name = ] 'output_database_name' ]   
     [ , [ @output_schema_name = ] 'output_schema_name' ]   
     [ , [ @output_table_name = ] 'output_table_name' ]   
     [ , [ @job_version = ] job_version OUTPUT ]
     [ , [ @max_parallelism = ] max_parallelism ]

参数Arguments

[ @job_name = ] 'job_name'[ @job_name = ] 'job_name'
步骤所属作业的名称。The name of the job to which the step belongs. job_name 为 nvarchar(128)。job_name is nvarchar(128).

[ @step_id = ] step_id[ @step_id = ] step_id
要修改的作业步骤的标识号。The identification number for the job step to be modified. 必须指定 step_id 或 step_name。Either step_id or step_name must be specified. step_id 为 int。step_id is an int.

[ @step_name = ] 'step_name'[ @step_name = ] 'step_name'
要修改的步骤的名称。The name of the step to be modified. 必须指定 step_id 或 step_name。Either step_id or step_name must be specified. step_name 为 nvarchar(128)。step_name is nvarchar(128).

[ @new_id = ] new_id[ @new_id = ] new_id
作业步骤的新序列标识号。The new sequence identification number for the job step. 步骤标识号从 1 开始连续递增。Step identification numbers start at 1 and increment without gaps. 如果某个步骤重新标记顺序,则其他步骤也会自动重新编号。If a step is reordered, then other steps will be automatically renumbered.

[ @new_name = ] 'new_name'[ @new_name = ] 'new_name'
步骤的新名称。The new name of the step. new_name 为 nvarchar(128)。new_name is nvarchar(128).

[ @command_type = ] 'command_type'[ @command_type = ] 'command_type'
由此 jobstep 执行的命令的类型。The type of command that is executed by this jobstep. command_type 为 nvarchar(50),默认值为 TSql,这意味着 @command_type 参数的值为 T-SQL 脚本。command_type is nvarchar(50), with a default value of TSql, meaning that the value of the @command_type parameter is a T-SQL script.

如果指定,此值必须为 TSql。If specified, the value must be TSql.

[ @command_source = ] 'command_source'[ @command_source = ] 'command_source'
命令存储位置的类型。The type of location where the command is stored. command_source 为 nvarchar(50),默认值为 Inline,这意味着 @command_source 参数的值为命令的文字文本。command_source is nvarchar(50), with a default value of Inline, meaning that the value of the @command_source parameter is the literal text of the command.

如果指定,此值必须为 Inline。If specified, the value must be Inline.

[ @command = ] 'command'[ @command = ] 'command'
命令必须是有效的 T-SQL 脚本,然后才能通过此作业步骤执行该命令。The command(s) must be valid T-SQL script and is then executed by this job step. command 为 nvarchar(max),默认值为 NULL。command is nvarchar(max), with a default of NULL.

[ @credential_name = ] 'credential_name'[ @credential_name = ] 'credential_name'
存储在此作业控制数据库中的数据库范围的凭据的名称,此控制数据库用于在执行此步骤时连接到目标组中的每个目标数据库。The name of the database scoped credential stored in this job control database that is used to connect to each of the target databases within the target group when this step is executed. credential_name 为 nvarchar(128)。credential_name is nvarchar(128).

[ @target_group_name = ] 'target-group_name'[ @target_group_name = ] 'target-group_name'
包含目标数据库(作业步骤将在其上执行)的目标组的名称。The name of the target group that contains the target databases that the job step will be executed on. target_group_name 为 nvarchar(128)。target_group_name is nvarchar(128).

[ @initial_retry_interval_seconds = ] initial_retry_interval_seconds[ @initial_retry_interval_seconds = ] initial_retry_interval_seconds
第一次重试尝试之前的延迟,前提是作业步骤在初始执行尝试时失败。The delay before the first retry attempt, if the job step fails on the initial execution attempt. initial_retry_interval_seconds 为 int,默认值为 1。initial_retry_interval_seconds is int, with default value of 1.

[ @maximum_retry_interval_seconds = ] maximum_retry_interval_seconds[ @maximum_retry_interval_seconds = ] maximum_retry_interval_seconds
重试尝试之间的最大延迟。The maximum delay between retry attempts. 如果重试之间的延迟大于此值,则会将其削减到此值。If the delay between retries would grow larger than this value, it is capped to this value instead. maximum_retry_interval_seconds 为 int,默认值为 120。maximum_retry_interval_seconds is int, with default value of 120.

[ @retry_interval_backoff_multiplier = ] retry_interval_backoff_multiplier[ @retry_interval_backoff_multiplier = ] retry_interval_backoff_multiplier
将要应用到重试延迟的乘数,前提是多个作业步骤执行尝试失败。The multiplier to apply to the retry delay if multiple job step execution attempts fail. 例如,如果首次重试的延迟为 5 秒,而回退乘数为 2.0,则第二次重试的延迟为 10 秒,第三次重试的延迟为 20 秒。For example, if the first retry had a delay of 5 second and the backoff multiplier is 2.0, then the second retry will have a delay of 10 seconds and the third retry will have a delay of 20 seconds. retry_interval_backoff_multiplier 为 real,默认值为 2.0。retry_interval_backoff_multiplier is real, with default value of 2.0.

[ @retry_attempts = ] retry_attempts[ @retry_attempts = ] retry_attempts
重试执行的次数,前提是初始尝试失败。The number of times to retry execution if the initial attempt fails. 例如,如果 retry_attempts 值为 10,则初始尝试次数为 1,重试尝试次数为 10,总尝试次数为 11。For example, if the retry_attempts value is 10, then there will be 1 initial attempt and 10 retry attempts, giving a total of 11 attempts. 如果最后的重试尝试失败,则作业执行操作会终止,生命周期状态为“失败”。If the final retry attempt fails, then the job execution will terminate with a lifecycle of Failed. retry_attempts 为 int,默认值为 10。retry_attempts is int, with default value of 10.

[ @step_timeout_seconds = ] step_timeout_seconds[ @step_timeout_seconds = ] step_timeout_seconds
允许步骤执行的最长时间。The maximum amount of time allowed for the step to execute. 如果超出此时间,则作业执行操作会终止,生命周期状态为“超时”。If this time is exceeded, then the job execution will terminate with a lifecycle of TimedOut. step_timeout_seconds 为 int,默认值为 43,200 秒(12 小时)。step_timeout_seconds is int, with default value of 43,200 seconds (12 hours).

[ @output_type = ] 'output_type'[ @output_type = ] 'output_type'
如果不为 null,则为将命令的首个结果集写入到其中的目标的类型。If not null, the type of destination that the command's first result set is written to. 若要将 output_type 的值重置为 NULL,请将此参数的值设置为 ''(空字符串)。To reset the value of output_type back to NULL, set this parameter's value to '' (empty string). output_type 为 nvarchar(50),默认值为 NULL。output_type is nvarchar(50), with a default of NULL.

如果指定,此值必须为 SqlDatabase。If specified, the value must be SqlDatabase.

[ @output_credential_name = ] 'output_credential_name'[ @output_credential_name = ] 'output_credential_name'
如果不为 null,则为数据库范围的凭据的名称,该凭据用于连接到输出目标数据库。If not null, the name of the database scoped credential that is used to connect to the output destination database. 如果 output_type 等于 SqlDatabase,则必须指定此项。Must be specified if output_type equals SqlDatabase. 若要将 output_credential_name 的值重置为 NULL,请将此参数的值设置为 ''(空字符串)。To reset the value of output_credential_name back to NULL, set this parameter's value to '' (empty string). output_credential_name 为 nvarchar(128),默认值为 NULL。output_credential_name is nvarchar(128), with a default value of NULL.

[ @output_server_name = ] 'output_server_name'[ @output_server_name = ] 'output_server_name'
如果不为 null,则为服务器的完全限定的 DNS 名称,该服务器包含输出目标数据库。If not null, the fully qualified DNS name of the server that contains the output destination database. 如果 output_type 等于 SqlDatabase,则必须指定此项。Must be specified if output_type equals SqlDatabase. 若要将 output_server_name 的值重置为 NULL,请将此参数的值设置为 ''(空字符串)。To reset the value of output_server_name back to NULL, set this parameter's value to '' (empty string). output_server_name 为 nvarchar(256),默认值为 NULL。output_server_name is nvarchar(256), with a default of NULL.

[ @output_database_name = ] 'output_database_name'[ @output_database_name = ] 'output_database_name'
如果不为 null,则为包含输出目标表的数据库的名称。If not null, the name of the database that contains the output destination table. 如果 output_type 等于 SqlDatabase,则必须指定此项。Must be specified if output_type equals SqlDatabase. 若要将 output_database_name 的值重置为 NULL,请将此参数的值设置为 ''(空字符串)。To reset the value of output_database_name back to NULL, set this parameter's value to '' (empty string). output_database_name 为 nvarchar(128),默认值为 NULL。output_database_name is nvarchar(128), with a default of NULL.

[ @output_schema_name = ] 'output_schema_name'[ @output_schema_name = ] 'output_schema_name'
如果不为 null,则为包含输出目标表的 SQL 架构的名称。If not null, the name of the SQL schema that contains the output destination table. 如果 output_type 等于 SqlDatabase,则默认值为 dbo。If output_type equals SqlDatabase, the default value is dbo. 若要将 output_schema_name 的值重置为 NULL,请将此参数的值设置为 ''(空字符串)。To reset the value of output_schema_name back to NULL, set this parameter's value to '' (empty string). output_schema_name 为 nvarchar(128)。output_schema_name is nvarchar(128).

[ @output_table_name = ] 'output_table_name'[ @output_table_name = ] 'output_table_name'
如果不为 null,则为将命令的首个结果集写入到其中的表的名称。If not null, the name of the table that the command's first result set will be written to. 如果此表不存在,则会根据返回结果集的架构来创建它。If the table doesn't already exist, it will be created based on the schema of the returning result-set. 如果 output_type 等于 SqlDatabase,则必须指定此项。Must be specified if output_type equals SqlDatabase. 若要将 output_server_name 的值重置为 NULL,请将此参数的值设置为 ''(空字符串)。To reset the value of output_server_name back to NULL, set this parameter's value to '' (empty string). output_table_name 为 nvarchar(128),默认值为 NULL。output_table_name is nvarchar(128), with a default value of NULL.

[ @job_version = ] job_version OUTPUT[ @job_version = ] job_version OUTPUT
一个输出参数,将会为其分配新作业版本号。Output parameter that will be assigned the new job version number. job_version 为 int。job_version is int.

[ @max_parallelism = ] max_parallelism OUTPUT[ @max_parallelism = ] max_parallelism OUTPUT
每个弹性池的最大并行度级别。The maximum level of parallelism per elastic pool. 如果设置此项,则作业步骤会受到限制,每个弹性池中可以在其上运行作业步骤的数据库会有一个最大数目。If set, then the job step will be restricted to only run on a maximum of that many databases per elastic pool. 这适用于每个特定的弹性池,该弹性池直接包括在目标组中,或者位于一个服务器中,而该服务器则包括在目标组中。This applies to each elastic pool that is either directly included in the target group or is inside a server that is included in the target group. 若要将 max_parallelism 的值重置为 null,请将此参数的值设置为 -1。To reset the value of max_parallelism back to null, set this parameter's value to -1. max_parallelism 为 int。max_parallelism is int.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

不会影响作业的正在进行的执行操作。Any in-progress executions of the job will not be affected. 当 sp_update_jobstep 成功时,作业的版本号会递增。When sp_update_jobstep succeeds, the job's version number is incremented. 下次执行作业时,会使用新版本。The next time the job is executed, the new version will be used.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users

sp_delete_jobstepsp_delete_jobstep

从作业中删除作业步骤。Removes a job step from a job.

语法Syntax

[jobs].sp_delete_jobstep [ @job_name = ] 'job_name'   
     [ , [ @step_id = ] step_id ]
     [ , [ @step_name = ] 'step_name' ]   
     [ , [ @job_version = ] job_version OUTPUT ]

参数Arguments

[ @job_name = ] 'job_name'[ @job_name = ] 'job_name'
要从其中删除步骤的作业的名称。The name of the job from which the step will be removed. job_name 为 nvarchar(128),没有默认值。job_name is nvarchar(128), with no default.

[ @step_id = ] step_id[ @step_id = ] step_id
要删除的作业步骤的标识号。The identification number for the job step to be deleted. 必须指定 step_id 或 step_name。Either step_id or step_name must be specified. step_id 为 int。step_id is an int.

[ @step_name = ] 'step_name'[ @step_name = ] 'step_name'
要删除的步骤的名称。The name of the step to be deleted. 必须指定 step_id 或 step_name。Either step_id or step_name must be specified. step_name 为 nvarchar(128)。step_name is nvarchar(128).

[ @job_version = ] job_version OUTPUT[ @job_version = ] job_version OUTPUT
一个输出参数,将会为其分配新作业版本号。Output parameter that will be assigned the new job version number. job_version 为 int。job_version is int.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

不会影响作业的正在进行的执行操作。Any in-progress executions of the job will not be affected. 当 sp_update_jobstep 成功时,作业的版本号会递增。When sp_update_jobstep succeeds, the job's version number is incremented. 下次执行作业时,会使用新版本。The next time the job is executed, the new version will be used.

其他作业步骤会自动重新编号,以填补已删除的作业步骤留下的空缺。The other job steps will be automatically renumbered to fill the gap left by the deleted job step.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性。Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

sp_start_jobsp_start_job

开始执行作业。Starts executing a job.

语法Syntax

[jobs].sp_start_job [ @job_name = ] 'job_name'   
     [ , [ @job_execution_id = ] job_execution_id OUTPUT ]   

参数Arguments

[ @job_name = ] 'job_name'[ @job_name = ] 'job_name'
要从其中删除步骤的作业的名称。The name of the job from which the step will be removed. job_name 为 nvarchar(128),没有默认值。job_name is nvarchar(128), with no default.

[ @job_execution_id = ] job_execution_id OUTPUT[ @job_execution_id = ] job_execution_id OUTPUT
一个输出参数,将会为其分配作业执行操作的 ID。job_version 为 uniqueidentifier。Output parameter that will be assigned the job execution's id. job_version is uniqueidentifier.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

无。None.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性。Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

sp_stop_jobsp_stop_job

停止作业的执行。Stops a job execution.

语法Syntax

[jobs].sp_stop_job [ @job_execution_id = ] ' job_execution_id '

参数Arguments

[ @job_execution_id = ] job_execution_id[ @job_execution_id = ] job_execution_id
要停止的作业执行操作的标识号。The identification number of the job execution to stop. job_execution_id 为 uniqueidentifier,默认值为 NULL。job_execution_id is uniqueidentifier, with default of NULL.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

无。None.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性。Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

sp_add_target_groupsp_add_target_group

添加目标组。Adds a target group.

语法Syntax

[jobs].sp_add_target_group [ @target_group_name = ] 'target_group_name'   
     [ , [ @target_group_id = ] target_group_id OUTPUT ]

参数Arguments

[ @target_group_name = ] 'target_group_name'[ @target_group_name = ] 'target_group_name'
要创建的目标组的名称。The name of the target group to create. target_group_name 为 nvarchar(128),没有默认值。target_group_name is nvarchar(128), with no default.

[ @target_group_id = ] target_group_id OUTPUT 分配给已成功创建的作业的目标组标识号。[ @target_group_id = ] target_group_id OUTPUT The target group identification number assigned to the job if created successfully. target_group_id 是类型为 uniqueidentifier 的输出变量,默认值为 NULL。target_group_id is an output variable of type uniqueidentifier, with a default of NULL.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

可以通过目标组轻松地将作业的目标确定为数据库集合。Target groups provide an easy way to target a job at a collection of databases.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性。Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

sp_delete_target_groupsp_delete_target_group

删除目标组。Deletes a target group.

语法Syntax

[jobs].sp_delete_target_group [ @target_group_name = ] 'target_group_name'

参数Arguments

[ @target_group_name = ] 'target_group_name'[ @target_group_name = ] 'target_group_name'
要删除的目标组的名称。The name of the target group to delete. target_group_name 为 nvarchar(128),没有默认值。target_group_name is nvarchar(128), with no default.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

无。None.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性。Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

sp_add_target_group_membersp_add_target_group_member

向目标组添加一个或一组数据库。Adds a database or group of databases to a target group.

语法Syntax

[jobs].sp_add_target_group_member [ @target_group_name = ] 'target_group_name'
         [ @membership_type = ] 'membership_type' ]   
        [ , [ @target_type = ] 'target_type' ]   
        [ , [ @refresh_credential_name = ] 'refresh_credential_name' ]   
        [ , [ @server_name = ] 'server_name' ]   
        [ , [ @database_name = ] 'database_name' ]   
        [ , [ @elastic_pool_name = ] 'elastic_pool_name' ]   
        [ , [ @shard_map_name = ] 'shard_map_name' ]   
        [ , [ @target_id = ] 'target_id' OUTPUT ]

参数Arguments

[ @target_group_name = ] 'target_group_name'[ @target_group_name = ] 'target_group_name'
要向其添加成员的目标组的名称。The name of the target group to which the member will be added. target_group_name 为 nvarchar(128),没有默认值。target_group_name is nvarchar(128), with no default.

[ @membership_type = ] 'membership_type'[ @membership_type = ] 'membership_type'
指定是包括目标组成员还是将其排除。Specifies if the target group member will be included or excluded. target_group_name 为 nvarchar(128),默认值为 'Include'。target_group_name is nvarchar(128), with default of 'Include'. target_group_name 的有效值为 'Include' 或 'Exclude'。Valid values for target_group_name are 'Include' or 'Exclude'.

[ @target_type = ] 'target_type'[ @target_type = ] 'target_type'
目标数据库或数据库集合的类型,其中包括一个服务器中的所有数据库、一个弹性池中的所有数据库、一个分片映射中的所有数据库,或者一个单独的数据库。The type of target database or collection of databases including all databases in a server, all databases in an Elastic pool, all databases in a shard map, or an individual database. target_type 为 nvarchar(128),没有默认值。target_type is nvarchar(128), with no default. target_type 的有效值为 'SqlServer'、'SqlElasticPool'、'SqlDatabase' 或 'SqlShardMap'。Valid values for target_type are 'SqlServer', 'SqlElasticPool', 'SqlDatabase', or 'SqlShardMap'.

[ @refresh_credential_name = ] 'refresh_credential_name'[ @refresh_credential_name = ] 'refresh_credential_name'
SQL 数据库服务器的名称。The name of the SQL Database server. refresh_credential_name 为 nvarchar(128),没有默认值。refresh_credential_name is nvarchar(128), with no default.

[ @server_name = ] 'server_name'[ @server_name = ] 'server_name'
应添加到指定目标组的 SQL 数据库服务器的名称。The name of the SQL Database server that should be added to the specified target group. 当 target_type 为 ‘SqlServer’ 时,应指定 server_name。server_name should be specified when target_type is ‘SqlServer’. server_name 为 nvarchar(128),没有默认值。server_name is nvarchar(128), with no default.

[ @database_name = ] 'database_name'[ @database_name = ] 'database_name'
应添加到指定目标组的数据库的名称。The name of the database that should be added to the specified target group. 当 target_type 为 'SqlDatabase' 时,应指定 database_name。database_name should be specified when target_type is 'SqlDatabase'. database_name 为 nvarchar(128),没有默认值。database_name is nvarchar(128), with no default.

[ @elastic_pool_name = ] 'elastic_pool_name'[ @elastic_pool_name = ] 'elastic_pool_name'
应添加到指定目标组的弹性池的名称。The name of the Elastic pool that should be added to the specified target group. 当 target_type 为 'SqlElasticPool' 时,应指定 elastic_pool_name。elastic_pool_name should be specified when target_type is 'SqlElasticPool'. elastic_pool_name 为 nvarchar(128),没有默认值。elastic_pool_name is nvarchar(128), with no default.

[ @shard_map_name = ] 'shard_map_name'[ @shard_map_name = ] 'shard_map_name'
应添加到指定目标组的分片映射池的名称。The name of the shard map pool that should be added to the specified target group. 当 target_type 为 'SqlSqlShardMap' 时,应指定 elastic_pool_name。elastic_pool_name should be specified when target_type is 'SqlSqlShardMap'. shard_map_name 为 nvarchar(128),没有默认值。shard_map_name is nvarchar(128), with no default.

[ @target_id = ] target_group_id OUTPUT[ @target_id = ] target_group_id OUTPUT
分配给目标组成员的目标标识号,前提是其已创建并添加到目标组。The target identification number assigned to the target group member if created added to the target group. target_id 是类型为 uniqueidentifier 的输出变量,默认值为 NULL。target_id is an output variable of type uniqueidentifier, with a default of NULL. 返回代码值 0(成功)或 1(失败)Return Code Values 0 (success) or 1 (failure)

备注Remarks

如果将 SQL 数据库服务器或弹性池包括在目标组中,则一旦执行作业,作业就会在 SQL 数据库服务器或弹性池中的所有单一数据库上执行。A job executes on all single databases within a SQL Database server or in an elastic pool at time of execution, when a SQL Database server or Elastic pool is included in the target group.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性。Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

示例Examples

以下示例将 London 和 NewYork 服务器中的所有数据库添加到“Servers Maintaining Customer Information”组。The following example adds all the databases in the London and NewYork servers to the group Servers Maintaining Customer Information. 必须在创建作业代理(在此示例中为 ElasticJobs)时连接到指定的作业数据库。You must connect to the jobs database specified when creating the job agent, in this case ElasticJobs.

--Connect to the jobs database specified when creating the job agent
USE ElasticJobs ; 
GO

-- Add a target group containing server(s)
EXEC jobs.sp_add_target_group @target_group_name =  N'Servers Maintaining Customer Information'
GO

-- Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name = N'Servers Maintaining Customer Information',
@target_type = N'SqlServer',
@refresh_credential_name=N'mymastercred', --credential required to refresh the databases in server
@server_name=N'London.database.chinacloudapi.cn' ;
GO

-- Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name = N'Servers Maintaining Customer Information',
@target_type = N'SqlServer',
@refresh_credential_name=N'mymastercred', --credential required to refresh the databases in server
@server_name=N'NewYork.database.chinacloudapi.cn' ;
GO

--View the recently added members to the target group
SELECT * FROM [jobs].target_group_members WHERE target_group_name= N'Servers Maintaining Customer Information';
GO

sp_delete_target_group_membersp_delete_target_group_member

从目标组中删除目标组成员。Removes a target group member from a target group.

语法Syntax

[jobs].sp_delete_target_group_member [ @target_group_name = ] 'target_group_name'
        [ , [ @target_id = ] 'target_id']

Arguments [ @target_group_name = ] 'target_group_name'Arguments [ @target_group_name = ] 'target_group_name'
要从其删除目标组成员的目标组的名称。The name of the target group from which to remove the target group member. target_group_name 为 nvarchar(128),没有默认值。target_group_name is nvarchar(128), with no default.

[ @target_id = ] target_id[ @target_id = ] target_id
分配给要删除的目标组成员的目标标识号。The target identification number assigned to the target group member to be removed. target_id 为 uniqueidentifier,默认值为 NULL。target_id is a uniqueidentifier, with a default of NULL.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

可以通过目标组轻松地将作业的目标确定为数据库集合。Target groups provide an easy way to target a job at a collection of databases.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性。Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

示例Examples

以下示例从“Servers Maintaining Customer Information”组删除 London 服务器。The following example removes the London server from the group Servers Maintaining Customer Information. 必须在创建作业代理(在此示例中为 ElasticJobs)时连接到指定的作业数据库。You must connect to the jobs database specified when creating the job agent, in this case ElasticJobs.

--Connect to the jobs database specified when creating the job agent
USE ElasticJobs ; 
GO

-- Retrieve the target_id for a target_group_members
declare @tid uniqueidentifier
SELECT @tid = target_id FROM [jobs].target_group_members WHERE target_group_name = 'Servers Maintaining Customer Information' and server_name = 'London.database.chinacloudapi.cn'

-- Remove a target group member of type server
EXEC jobs.sp_delete_target_group_member
@target_group_name = N'Servers Maintaining Customer Information',
@target_id = @tid
GO

sp_purge_jobhistorysp_purge_jobhistory

删除作业的历史记录。Removes the history records for a job.

语法Syntax

[jobs].sp_purge_jobhistory [ @job_name = ] 'job_name'   
      [ , [ @job_id = ] job_id ]
      [ , [ @oldest_date = ] oldest_date []

参数Arguments

[ @job_name = ] 'job_name'[ @job_name = ] 'job_name'
要删除其历史记录的作业的名称。The name of the job for which to delete the history records. job_name 为 nvarchar(128),默认值为 NULL。job_name is nvarchar(128), with a default of NULL. 必须指定 job_id 或 job_name,但不得二者同时指定。Either job_id or job_name must be specified, but both cannot be specified.

[ @job_id = ] job_id[ @job_id = ] job_id
要删除其记录的作业的作业标识号。The job identification number of the job for the records to be deleted. job_id 为 uniqueidentifier,默认值为 NULL。job_id is uniqueidentifier, with a default of NULL. 必须指定 job_id 或 job_name,但不得二者同时指定。Either job_id or job_name must be specified, but both cannot be specified.

[ @oldest_date = ] oldest_date[ @oldest_date = ] oldest_date
要保留在历史记录中的最旧记录。The oldest record to retain in the history. oldest_date 为 DATETIME2,默认值为 NULL。oldest_date is DATETIME2, with a default of NULL. 指定 oldest_date 后,sp_purge_jobhistory 仅删除比指定值更早的记录。When oldest_date is specified, sp_purge_jobhistory only removes records that are older than the value specified.

返回代码值Return Code Values

0(成功)或 1(失败) 备注 可以通过目标组轻松地将作业的目标确定为数据库集合。0 (success) or 1 (failure) Remarks Target groups provide an easy way to target a job at a collection of databases.

权限Permissions

默认情况下,sysadmin 固定服务器角色的成员可以执行此存储过程。By default, members of the sysadmin fixed server role can execute this stored procedure. 它们将用户限制为只能监视作业,你可授予用户相应权限,使之成为作业代理数据库(在创建作业代理时指定)中以下数据库角色的成员:They restrict a user to just be able to monitor jobs, you can grant the user to be part of the following database role in the job agent database specified when creating the job agent:

  • jobs_readerjobs_reader

若要详细了解这些角色的权限,请参阅本文档中的“权限”部分。For details about the permissions of these roles, see the Permission section in this document. 仅 sysadmin 的成员可以使用此存储过程来编辑其他用户拥有的作业的属性。Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

示例Examples

以下示例将 London 和 NewYork 服务器中的所有数据库添加到“Servers Maintaining Customer Information”组。The following example adds all the databases in the London and NewYork servers to the group Servers Maintaining Customer Information. 必须在创建作业代理(在此示例中为 ElasticJobs)时连接到指定的作业数据库。You must connect to the jobs database specified when creating the job agent, in this case ElasticJobs.

--Connect to the jobs database specified when creating the job agent

EXEC sp_delete_target_group_member   
    @target_group_name = N'Servers Maintaining Customer Information',  
    @server_name = N'London.database.chinacloudapi.cn';  
GO

作业视图Job views

以下视图在作业数据库中提供。The following views are available in the jobs database.

查看View 说明Description
job_executionsjob_executions 显示作业执行历史记录。Shows job execution history.
jobsjobs 显示所有作业。Shows all jobs.
job_versionsjob_versions 显示所有作业版本。Shows all job versions.
jobstepsjobsteps 显示每项作业的当前版本中的所有步骤。Shows all steps in the current version of each job.
jobstep_versionsjobstep_versions 显示每项作业的所有版本中的所有步骤。Shows all steps in all versions of each job.
target_groupstarget_groups 显示所有目标组。Shows all target groups.
target_group_memberstarget_group_members 显示所有目标组的所有成员。Shows all members of all target groups.

job_executions 视图job_executions view

[jobs].[job_executions][jobs].[job_executions]

显示作业执行历史记录。Shows job execution history.

列名称Column name 数据类型Data type 说明Description
job_execution_idjob_execution_id uniqueidentifieruniqueidentifier 一个作业执行操作实例的唯一 ID。Unique ID of an instance of a job execution.
job_namejob_name nvarchar(128)nvarchar(128) 作业的名称。Name of the job.
job_idjob_id uniqueidentifieruniqueidentifier 作业的唯一 ID。Unique ID of the job.
job_versionjob_version intint 作业的版本(每次修改作业时自动更新)。Version of the job (automatically updated each time the job is modified).
step_idstep_id intint 步骤的唯一(就此作业来说)标识符。Unique (for this job) identifier for the step. NULL 指示这是父作业执行操作。NULL indicates this is the parent job execution.
is_activeis_active bitbit 指示信息是处于活动状态还是非活动状态。Indicates whether information is active or inactive. 1 指示作业处于活动状态,0 指示作业处于非活动状态。1 indicates active jobs, and 0 indicates inactive.
lifecyclelifecycle nvarchar(50)nvarchar(50) 指示作业状态的值:'Created'、'In Progress'、'Failed'、'Succeeded'、'Skipped'、'SucceededWithSkipped'Value indicating the status of the job:'Created','In Progress', 'Failed', 'Succeeded', 'Skipped','SucceededWithSkipped'
create_timecreate_time datetime2(7)datetime2(7) 作业的创建日期和时间。Date and time the job was created.
start_timestart_time datetime2(7)datetime2(7) 作业开始执行的日期和时间。Date and time the job started execution. 如果作业尚未执行,则为 NULL。NULL if the job has not yet been executed.
end_timeend_time datetime2(7)datetime2(7) 作业执行完毕的日期和时间。Date and time the job finished execution. 如果作业尚未执行或尚未执行完毕,则为 NULL。NULL if the job has not yet been executed or has not yet completed execution.
current_attemptscurrent_attempts intint 步骤重试的次数。Number of times the step was retried. 父作业执行次数将为 0,子作业执行次数将为 1 或更大值,具体取决于执行策略。Parent job will be 0, child job executions will be 1 or greater based on the execution policy.
current_attempt_start_timecurrent_attempt_start_time datetime2(7)datetime2(7) 作业开始执行的日期和时间。Date and time the job started execution. NULL 指示这是父作业执行操作。NULL indicates this is the parent job execution.
last_messagelast_message nvarchar(max)nvarchar(max) 作业或步骤历史记录消息。Job or step history message.
target_typetarget_type nvarchar(128)nvarchar(128) 目标数据库或数据库集合的类型,其中包括一个服务器中的所有数据库、一个弹性池中的所有数据库,或者单个数据库。Type of target database or collection of databases including all databases in a server, all databases in an Elastic pool or a database. target_type 的有效值为 'SqlServer'、'SqlElasticPool' 或 'SqlDatabase'。Valid values for target_type are 'SqlServer', 'SqlElasticPool' or 'SqlDatabase'. NULL 指示这是父作业执行操作。NULL indicates this is the parent job execution.
target_idtarget_id uniqueidentifieruniqueidentifier 目标组成员的唯一 ID。Unique ID of the target group member. NULL 指示这是父作业执行操作。NULL indicates this is the parent job execution.
target_group_nametarget_group_name nvarchar(128)nvarchar(128) 目标组的名称。Name of the target group. NULL 指示这是父作业执行操作。NULL indicates this is the parent job execution.
target_server_nametarget_server_name nvarchar(256)nvarchar(256) 包含在目标组中的 SQL 数据库服务器的名称。Name of the SQL Database server contained in the target group. 仅当 target_type 为 ‘SqlServer’ 时指定。Specified only if target_type is ‘SqlServer’. NULL 指示这是父作业执行操作。NULL indicates this is the parent job execution.
target_database_nametarget_database_name nvarchar(128)nvarchar(128) 包含在目标组中的数据库的名称。Name of the database contained in the target group. 仅当 target_type 为 'SqlDatabase' 时指定。Specified only when target_type is 'SqlDatabase'. NULL 指示这是父作业执行操作。NULL indicates this is the parent job execution.

作业视图jobs view

[jobs].[jobs][jobs].[jobs]

显示所有作业。Shows all jobs.

列名称Column name 数据类型Data type 说明Description
job_namejob_name nvarchar(128)nvarchar(128) 作业的名称。Name of the job.
job_idjob_id uniqueidentifieruniqueidentifier 作业的唯一 ID。Unique ID of the job.
job_versionjob_version intint 作业的版本(每次修改作业时自动更新)。Version of the job (automatically updated each time the job is modified).
descriptiondescription nvarchar(512)nvarchar(512) 作业的说明。Description for the job. enabled 为 bit 指示作业是已启用还是已禁用。enabled bit Indicates whether the job is enabled or disabled. 1 指示作业已启用,0 指示作业已禁用。1 indicates enabled jobs, and 0 indicates disabled jobs.
schedule_interval_typeschedule_interval_type nvarchar(50)nvarchar(50) 指示何时执行作业的值:'Once'、'Minutes'、'Hours'、'Days'、'Weeks'、'Months'Value indicating when the job is to be executed:'Once', 'Minutes', 'Hours', 'Days', 'Weeks', 'Months'
schedule_interval_countschedule_interval_count intint 每次执行作业时,其间会出现的 schedule_interval_type 期间数。Number of schedule_interval_type periods to occur between each execution of the job.
schedule_start_timeschedule_start_time datetime2(7)datetime2(7) 作业上次开始执行的日期和时间。Date and time the job was last started execution.
schedule_end_timeschedule_end_time datetime2(7)datetime2(7) 作业上次完成执行的日期和时间。Date and time the job was last completed execution.

job_versions 视图job_versions view

[jobs].[job_versions][jobs].[job_versions]

显示所有作业版本。Shows all job versions.

列名称Column name 数据类型Data type 说明Description
job_namejob_name nvarchar(128)nvarchar(128) 作业的名称。Name of the job.
job_idjob_id uniqueidentifieruniqueidentifier 作业的唯一 ID。Unique ID of the job.
job_versionjob_version intint 作业的版本(每次修改作业时自动更新)。Version of the job (automatically updated each time the job is modified).

jobsteps 视图jobsteps view

[jobs].[jobsteps][jobs].[jobsteps]

显示每项作业的当前版本中的所有步骤。Shows all steps in the current version of each job.

列名称Column name 数据类型Data type 说明Description
job_namejob_name nvarchar(128)nvarchar(128) 作业的名称。Name of the job.
job_idjob_id uniqueidentifieruniqueidentifier 作业的唯一 ID。Unique ID of the job.
job_versionjob_version intint 作业的版本(每次修改作业时自动更新)。Version of the job (automatically updated each time the job is modified).
step_idstep_id intint 步骤的唯一(就此作业来说)标识符。Unique (for this job) identifier for the step.
step_namestep_name nvarchar(128)nvarchar(128) 步骤的唯一(就此作业来说)名称。Unique (for this job) name for the step.
command_typecommand_type nvarchar(50)nvarchar(50) 要在作业步骤中执行的命令的类型。Type of command to execute in the job step. 就 v1 来说,值必须等于 'TSql'(默认设置)。For v1, value must equal to and defaults to 'TSql'.
command_sourcecommand_source nvarchar(50)nvarchar(50) 命令的位置。Location of the command. 就 v1 来说,'Inline' 是默认值,也是唯一接受的值。For v1, 'Inline' is the default and only accepted value.
commandcommand nvarchar(max)nvarchar(max) 将要由弹性作业按 command_type 执行的命令。The commands to be executed by Elastic jobs through command_type.
credential_namecredential_name nvarchar(128)nvarchar(128) 用于执行作业的数据库范围的凭据的名称。Name of the database scoped credential used to execution the job.
target_group_nametarget_group_name nvarchar(128)nvarchar(128) 目标组的名称。Name of the target group.
target_group_idtarget_group_id uniqueidentifieruniqueidentifier 目标组的唯一 ID。Unique ID of the target group.
initial_retry_interval_secondsinitial_retry_interval_seconds intint 首次重试尝试之前的延迟。The delay before the first retry attempt. 默认值为 1。Default value is 1.
maximum_retry_interval_secondsmaximum_retry_interval_seconds intint 重试尝试之间的最大延迟。The maximum delay between retry attempts. 如果重试之间的延迟大于此值,则会将其削减到此值。If the delay between retries would grow larger than this value, it is capped to this value instead. 默认值为 120。Default value is 120.
retry_interval_backoff_multiplierretry_interval_backoff_multiplier realreal 将要应用到重试延迟的乘数,前提是多个作业步骤执行尝试失败。The multiplier to apply to the retry delay if multiple job step execution attempts fail. 默认值为 2.0。Default value is 2.0.
retry_attemptsretry_attempts intint 在步骤失败的情况下可以使用的重试尝试次数。The number of retry attempts to use if this step fails. 默认值为 0,表示不允许重试尝试。Default of 10, which indicates no retry attempts.
step_timeout_secondsstep_timeout_seconds intint 两次重试尝试之间的时间(以分钟为单位)。The amount of time in minutes between retry attempts. 默认值为 0,表示时间间隔为 0 分钟。The default is 0, which indicates a 0-minute interval.
output_typeoutput_type nvarchar(11)nvarchar(11) 命令的位置。Location of the command. 在当前的预览版中,'Inline' 是默认值,也是唯一接受的值。In the current preview, 'Inline' is the default and only accepted value.
output_credential_nameoutput_credential_name nvarchar(128)nvarchar(128) 需要存储结果集时,用于连接到目标服务器的凭据的名称。Name of the credentials to be used to connect to the destination server to store the results set.
output_subscription_idoutput_subscription_id uniqueidentifieruniqueidentifier 执行查询后,所得结果集的目标服务器\数据库的订阅的唯一 ID。Unique ID of the subscription of the destination server\database for the results set from the query execution.
output_resource_group_nameoutput_resource_group_name nvarchar(128)nvarchar(128) 目标服务器所在资源组的名称。Resource group name where the destination server resides.
output_server_nameoutput_server_name nvarchar(256)nvarchar(256) 结果集的目标服务器的名称。Name of the destination server for the results set.
output_database_nameoutput_database_name nvarchar(128)nvarchar(128) 结果集的目标数据库的名称。Name of the destination database for the results set.
output_schema_nameoutput_schema_name nvarchar(max)nvarchar(max) 目标架构的名称。Name of the destination schema. 默认为 dbo(如果未指定)。Defaults to dbo, if not specified.
output_table_nameoutput_table_name nvarchar(max)nvarchar(max) 表名,用于存储查询结果的结果集。Name of the table to store the results set from the query results. 如果表不存在,则会根据结果集的架构自动创建表。Table will be created automatically based on the schema of the results set if it doesn't already exist. 架构必须与结果集的架构匹配。Schema must match the schema of the results set.
max_parallelismmax_parallelism intint 每次在弹性池的数据库上执行此作业步骤时,该弹性池允许的最大数据库数。The maximum number of databases per elastic pool that the job step will be run on at a time. 默认值为 NULL,这意味着没有限制。The default is NULL, meaning no limit.

jobstep_versions 视图jobstep_versions view

[jobs].[jobstep_versions][jobs].[jobstep_versions]

显示每项作业的所有版本中的所有步骤。Shows all steps in all versions of each job. 架构与 jobsteps 相同。The schema is identical to jobsteps.

target_groups 视图target_groups view

[jobs].[target_groups][jobs].[target_groups]

列出所有目标组。Lists all target groups.

列名称Column name 数据类型Data type 说明Description
target_group_nametarget_group_name nvarchar(128)nvarchar(128) 目标组(数据库集合)的名称。The name of the target group, a collection of databases.
target_group_idtarget_group_id uniqueidentifieruniqueidentifier 目标组的唯一 ID。Unique ID of the target group.

target_groups_members 视图target_groups_members view

[jobs].[target_groups_members][jobs].[target_groups_members]

显示所有目标组的所有成员。Shows all members of all target groups.

列名称Column name 数据类型Data type 说明Description
target_group_nametarget_group_name nvarchar(128nvarchar(128 目标组(数据库集合)的名称。The name of the target group, a collection of databases.
target_group_idtarget_group_id uniqueidentifieruniqueidentifier 目标组的唯一 ID。Unique ID of the target group.
membership_typemembership_type intint 指定是在目标组中包括目标组成员还是将其排除。Specifies if the target group member is included or excluded in the target group. target_group_name 的有效值为 'Include' 或 'Exclude'。Valid values for target_group_name are 'Include' or 'Exclude'.
target_typetarget_type nvarchar(128)nvarchar(128) 目标数据库或数据库集合的类型,其中包括一个服务器中的所有数据库、一个弹性池中的所有数据库,或者单个数据库。Type of target database or collection of databases including all databases in a server, all databases in an Elastic pool or a database. target_type 的有效值为 'SqlServer'、'SqlElasticPool'、'SqlDatabase' 或 'SqlShardMap'。Valid values for target_type are 'SqlServer', 'SqlElasticPool', 'SqlDatabase', or 'SqlShardMap'.
target_idtarget_id uniqueidentifieruniqueidentifier 目标组成员的唯一 ID。Unique ID of the target group member.
refresh_credential_namerefresh_credential_name nvarchar(128)nvarchar(128) 用于连接到目标组成员的数据库范围的凭据的名称。Name of the database scoped credential used to connect to the target group member.
subscription_idsubscription_id uniqueidentifieruniqueidentifier 订阅的唯一 ID。Unique ID of the subscription.
resource_group_nameresource_group_name nvarchar(128)nvarchar(128) 目标组成员所在资源组的名称。Name of the resource group in which the target group member resides.
server_nameserver_name nvarchar(128)nvarchar(128) 包含在目标组中的 SQL 数据库服务器的名称。Name of the SQL Database server contained in the target group. 仅当 target_type 为 'SqlServer' 时指定。Specified only if target_type is 'SqlServer'.
database_namedatabase_name nvarchar(128)nvarchar(128) 包含在目标组中的数据库的名称。Name of the database contained in the target group. 仅当 target_type 为 'SqlDatabase' 时指定。Specified only when target_type is 'SqlDatabase'.
elastic_pool_nameelastic_pool_name nvarchar(128)nvarchar(128) 包含在目标组中的弹性池的名称。Name of the Elastic pool contained in the target group. 仅当 target_type 为 'SqlElasticPool' 时指定。Specified only when target_type is 'SqlElasticPool'.
shard_map_nameshard_map_name nvarchar(128)nvarchar(128) 包含在目标组中的分片映射的名称。Name of the shard map contained in the target group. 仅当 target_type 为 'SqlShardMap' 时指定。Specified only when target_type is 'SqlShardMap'.

资源Resources

后续步骤Next steps