To create a jobThe first step in implementing a job is to define it. The primary attributes of a job are:
Every job must have a name. Job names must be unique only if they originate from the same server. For example, if SQL Server is set up to run on a target server, a locally created job and a downloaded job from a master server can share the same name. A job name can be no more than 128 characters.
By default, a job is enabled when it is created.
You can disable a job if you do not want it to run. For example, if you want to test a job before it is scheduled to run, you must first disable the job.
To create a job
To disable a job
Job categories allow you to organize your jobs for easy filtering and grouping. For example, you can organize jobs in to categories that correspond to company departments, such as payroll, human resources, or finance.
During installation, SQL Server creates these local job categories within SQL Server Agent:
By default, local jobs are assigned to the [Uncategorized (Local)] job category.
There is also one default master server job category: [Uncategorized (Multi-Server)]. Multiserver categories exist only on a master server. When a multiserver job is downloaded, its category is changed to Jobs from MSX at the target server.
Note Target servers and single servers (a server that is neither a target nor a master server) do not display the [Uncategorized (Multiserver)] category because all jobs running on target and single servers are considered to be local.
To create a job category
To delete a job category
To assign a job to a job category
To change the membership of a job category
To list category information
The job owner is the individual who creates a job or for whom the job is created. By default, SQL Server Agent populates the owner information with the SQL Server login ID of the job creator. Only members of the sysadmin role can give ownership of a job to another user or can alter the attributes of a job owned by another user.
The job owner adds a security layer to the job definition. Only the job owner or a member of the sysadmin role can change the definition of an existing job or start and stop the job.
To create a job
To give others ownership of a job
A job can be run by other users on both the local and remote computers running SQL Server. By writing a description at the time a job is created, the job owner can ensure that other users will understand quickly the purpose of the job.
A job description must not exceed 512 characters.
To create a job
A job step is an action that the job takes on a database or a server. Every job must have at least one job step. Job steps can be operating system commands, Transact-SQL statements, Microsoft® ActiveX Script™, or replication tasks.
When you create a CmdExec job step, you must specify:
To create a CmdExec job step
To reset SQLAgentCmdExec permissions
To reset the SQLAgentCmdExec account password
You can also open an existing Transact-SQL file as the command for the job step.
Members of the sysadmin role can write job steps to run in the context of another database user. For example, a system administrator can run a job that creates database objects in the pubs database on behalf of another database user.
Note A single Transact-SQL job step can contain multiple batches. Transact-SQL job steps can contain embedded GO commands, just like osql.exe.
To create a Transact-SQL job step
To define Transact-SQL job step options
When a job step command is an ActiveX Script, you can use the SQLActiveScriptHost object to print output to the step history or create objects. SQLActiveScriptHost is a global object that is introduced by SQL Server Agent ActiveX Script hosting system into the script name space. The object has two methods (PrintObject and CreateObject) that can be used to print output produced by the job step, or create a COM object. These examples show how Active Scripting works in VBScript and PerlScript.
Rem VBScript job step example:
Sub main()
Set DMOServer = CreateObject("SQLDMO.SQLServer")
DMOServer.LoginSecure = True
DMOServer.Connect "myserver"
Print DMOServer.Name
DMOServer.Disconnect
Set DMOServer = Nothing
End Sub
#PerlScript job step example:
Sub main()
{
$DMOServer = $SQLActiveScriptHost->CreateObject("SQLDMO.SQLServer");
$DMOServer->Connect("myserver", "sa");
$SQLActiveScriptHost->Print($DMOServer->Name);
$DMOServer->Disconnect();
$DMOServer = undef;
}
When you create an Active Scripting job step, you must:
You can also open an existing ActiveX Script file as the command for the job step. Active script commands can alternatively be externally compiled (for example, using Microsoft Visual Basic®) and then run as CmdExec executables.
To create an Active Script job step
Replication Job Steps
When you create a publication using replication, replication jobs are created automatically. The type of replication (snapshot, transactional, or merge) determines the type of job that is created.
Job steps of replication jobs execute one of these replication agents:
When replication is set up, the Distribution and Log Reader Agents run continuously after SQL Server Agent is started. The Merge and Snapshot Agents can be run on command or according to a schedule.
Note It is recommended that you use Replication Monitor to control replication job steps.
If your job has more than one job step, you must impose an order of execution on the job steps. This is called control-of-flow. You can add new job steps and rearrange the flow of job steps at any time. The changes take effect the next time the job is run. This illustration shows a control-of-flow for a database backup job.
You define a control-of-flow action for the success and failure of each job step. You must specify the action to be taken when a job step succeeds and when a job step fails. You can also define the number of and interval between retry attempts for failed job steps.
Job steps must be atomic. A job cannot pass Boolean values, data, or numeric values between job steps. You can pass values from one Transact-SQL job step to another by using permanent tables or global temporary tables. You can pass values from one CmdExec job step to another by using files.
Note If you create looping job steps (job step 1 is followed by job step 2, then job step 2 returns to job step 1), a warning message appears when the job is created using SQL Server Enterprise Manager.
SQL Server Agent records job and job step execution information in the job history.
To set job step success or failure flow
To set up the job history log
To view the job history
| Monitoring Replication | Monitoring Replication Agents |
| sysjobsteps | sysjobhistory |