Jobs

Jobs are the cornerstone of SQL Server automation. Managed from the Jobs folder of the SQL Server Agent folder, jobs can be single or multistep operations, involving any combination of T-SQL, operating system commands, ActiveX scripts, or replication tasks .

Defining Job Properties

Right-clicking the Jobs folder and selecting New Job will bring up the Job Property dialog box. On the General tab, enter a name for the job and make sure the Enabled check box is selected. You can then select a category for the job. Categories are used to organize similar jobs; after you select a category, clicking the Ellipse button ( ) beside it will show other jobs in the same category. Next is the Owner drop-down box. By default, this is the ID of the SQL Login creating the job, but if you are the administrator, you can assign the job to someone else. A Description field is also available and, if multiserver jobs are enabled, you can select on which servers the job will run. Multiserver jobs are covered in a following section of this chapter. Figure 18.6 shows the General tab of the Job properties.

Figure 18.6. The Job Properties General tab.

Defining Job Steps

The Steps tab defines the actual actions that the job consists of. From here, job steps can be added, edited, deleted, and moved up or down in the step hierarchy. The division of jobs into steps is what makes this such a powerful tool. Each job step can be defined ”on success or on failure ”to exit the job reporting failure, exit the job reporting success, go to the next step, or jump to any other step. This creates a go-no-go sequence that is limited only by your ingenuity in what it can do. Figure 18.7 shows a job defined with two steps. The first step attempts to back up the Pubs database to tape. If the first job fails (perhaps you forgot to load the tape), it goes on the next step, which performs a backup to disk.

Figure 18.7. The Job Properties Steps tab.

When you select New, Insert or Edit, the Edit Job Step dialog box is presented. On the General tab, enter a name for the step, the type of command (T-SQL, CmdExec, and so on), the database, and the actual command to be performed. The Open button allows you to import the command from a file, and parse checks the statement for correct syntax. Figure 18.8 shows the General tab of the Job Step dialog Box.

Figure 18.8. The General tab of the Job Step dialog box.

The Advanced tab is where you set the action of the step after success or failure, and the number and interval for retry attempts if a failure occurs. From the T-SQL Command Options section, you can also name a file to accept the output of T-SQL or ActiveScript commands, and optionally append the output to the job history. A drop-down box is also provided to select the user under which the command should run. Only a system administrator can choose to run the command as a different user . Figure 18.9 shows the Advanced tab of the Job Step dialog box.

Figure 18.9. The Advanced tab of the Job Step dialog box.

Multistep Jobs Versus Multiple Jobs

When do you create a multistep job, as opposed to multiple separate jobs? The idea behind multistep jobs is not so you can perform every task in your database in a single job. The steps in a multistep job should be related or dependent on the other steps in the job. A job that has two steps that each back up a different database should really have been created as two jobs. However, a job that consists of steps that back up the database, import data, and then update a Web page with the new data would be logical, as dependencies could be set on how to proceed if the previous step failed.

Defining Job Schedules

When the job will run is set from the Schedules tab. When creating schedules, you have the option of running the job when the SQL Server Agent starts it (as it would in an alert condition), when the CPU reaches a predefined idle level, once at a specified time, or, as is most common, at regularly scheduled intervals. Scheduling is taken a step further by allowing you to define multiple schedules in any combination of methods . For example, a job could run once a day at 3:00 a.m., or if an alert calls the Agent to run it. The example in Figure 18.10 shows a backup with different schedules for weekdays and the weekend .

Figure 18.10. The Job Properties Schedules tab.

Defining Job Notifications

The Notifications tab is where you configure how notifications will be handled. Notifications can be sent by e-mail, pager, or NET SEND on job completion, failure, or success. For example, a notification might send an e-mail if the job succeeds, but page someone if it fails. You can also set jobs to report to the Windows Application log. Jobs designed to run only once can be set to be automatically deleted. Figure 18.11 shows the Notifications tab.

Figure 18.11. The Job Properties Notifications tab.

Viewing Job History

To view the history for a job, right-click the job and select View Job History. By default, the SQL Server Agent maintains up to 100 rows of history for each job, to a maximum of 1,000 rows in total. This limit can be configured from the Job System tab of the SQL Server Agent property page. The job history is an excellent tool for troubleshooting problematic jobs.