Jobs | Microsoft SQL Server 2000 Administrators Companion

3 4

Jobs are administrative tasks that are defined once and can be run many times. You can run a job manually, or you can schedule the job to be executed by SQL Server at a specified time, on a regular schedule, or when an alert occurs. (Alerts are described in the section "Alerts" later in this chapter.) Jobs can consist of Transact-SQL (T-SQL) statements, Microsoft Windows NT or Microsoft Windows 2000 commands, executable programs, or Microsoft ActiveX scripts. Jobs are also automatically created for you when you use replication or when you create a database maintenance plan. A job can consist of a single step or many steps, and each step can be a call to a more complex set of steps—for example, a call to a stored procedure. SQL Server automatically monitors jobs for success or failure; you can set alerts to be sent out for either case.

Jobs can be run locally on a server, or if you have multiple servers on a network, you can designate one of the servers as the master server and designate the remaining servers as target servers. The master server stores the job definitions for all of the servers and acts as a job clearinghouse to coordinate all job activity. Each target server periodically connects to the master server, updates its job list if any jobs have changed, downloads any new jobs from the master server, and then disconnects to run those jobs. When a target server completes a job, it reconnects to the master server and reports its completion status.

Let's look at a situation in which you might want to create a job. Assume you have a database table that keeps a record of each transaction made in a banking environment, such as deposits, withdrawals, and money transfers. Each record has a timestamp column that indicates when the transaction occurred. This table will grow continuously and will need to be deleted periodically. To delete rows from this table, you could write a small stored procedure that uses the DELETE statement to delete rows that are older than two months, assuming the bank must keep records for only two months. Then you could create a job to run this stored procedure once each week on Sunday night, for example. In this way, you can ensure that the table will not grow endlessly. This saves disk space and usually improves performance as well. With less data in a table to search through when performing a query, SQL Server can finish the query faster. Now let's look at the details of how to create a job.

NOTE
The SQLServerAgent service must be running in order for your jobs to function.

Creating a Job

To define a job, you can use Enterprise Manager, T-SQL scripts, the Create Job Wizard, or SQL-Distributed Management Objects (SQL-DMO). Because the SQL-DMO method is programming related, it is beyond the scope of this book. You will learn about the other three methods for creating jobs in this section.

MORE INFO
For information about using SQL-DMO to create jobs, look up "jobs" in the Books Online index and select "Creating SQL Server Agent Jobs (SQL-DMO)" in the Topics Found dialog box.

Using Enterprise Manager

First let's create a job by using Enterprise Manager. One of the most common uses of a job is to run database backups. (This can also be accomplished using the Maintenance Plan Wizard as discussed in Chapter 30.) The following example creates a job to back up the MyDB database. It schedules the backup to run every night at 11 P.M. and records the success or failure of the backup job in the Windows NT or Windows 2000 application event log and in an output file. To create this job, which we will name MyDB_backup_job, follow these steps:

In the left pane of Enterprise Manager, expand a server folder, expand the Management folder, and then expand the SQL Server Agent folder. Right-click Jobs and choose New Job from the shortcut menu. The New Job Properties window appears, as shown in Figure 31-1.

Figure 31-1. The General tab of the New Job Properties window.
On the General tab, set the following options:
- Name Type the name of the job—in this case, MyDB_backup_job—in the Name text box. The job name can have up to 128 characters. Each job on a server must have a unique name. Be sure to use a descriptive name.
- Enabled You use the Enabled check box to specify whether the job should be enabled or disabled. You might want to initially disable the job to test it manually to ensure it functions properly. When you have tested the job and know that it is working correctly, use this check box to enable the job so that it will run as scheduled.
- Category Select a category for the job—in this case, we'll use the default category, Uncategorized (Local). You can choose from the job categories that are created when you install SQL Server, or you can create your own categories. (To learn how to create a new category, see the section "Creating a New Category" later in this section.) The installed categories are Uncategorized (Local), Database Maintenance, Full Text, Web Assistant, and the 10 categories for replication. Categories are used to group related jobs. For example, you can group all jobs that are used to perform database maintenance in one category, or you can group jobs by department, such as accounting, sales, and marketing. Categories enable you to keep track of multiple jobs—you don't have to search through the entire list of jobs when you're concerned with only a portion of the jobs.
- Owner The owner is the user who creates the job or the user for whom the job is created. Only sysadmin roles can change who owns a job or can alter a job owned by another user. (SQL Server roles are explained in Chapter 34.) All sysadmin roles and the job owner can alter a job definition and start and stop a job. In the Owner drop-down list, always select the user who will run the job. For this example, that's the same person who is creating the job, so the correct owner is automatically selected, and you can leave the setting as it is.
- Description In the Description text box, you identify what tasks a job performs and the purpose of the job. You should always provide a description. A description allows other users to quickly determine the function of the job. The description can be up to 512 characters long.
- Target Local Server If you click this option, the job will be run on the local server only. If you have remote servers connected to this server, the option Target Multiple Servers will be available. Click it to specify remote servers on which this job should be run as well.
The completed General tab for our sample job is shown in Figure 31-2.

Figure 31-2. The filled-in General tab.
Click the Steps tab and click New to display the New Job Step dialog box, shown in Figure 31-3. Job steps are the commands or statements that define the tasks of the job. Every job must have at least one job step and can have multiple steps. On the General tab of the New Job Step dialog box, enter the following information:
- In the Step Name text box, type the step name—in this case, type MyDB_backup.
- In the Type drop-down list, select a type of step to be performed. For this example, select Transact-SQL Script (TSQL) because we will use T-SQL commands to perform the job. The other choices are ActiveX Script, Operating System Command, Replication Distributor, Replication Transaction-Log Reader, Replication Merge, QueueReader, and Replication Snapshot.
- Select the name of the database that this job will run on in the Database drop-down list. For this example, select the MyDB database.
- Type the commands you want to be part of the step in the Command text box. For this example, these commands are the T-SQL commands to back up the MyDB database to a backup device named MyDB_backup1. (This backup device must be created beforehand. Creating backup devices is detailed in Chapter 32. Also, ours is a simple example in which the database backup will be written to the same file each night. In practice, you should use a database maintenance plan, as described in Chapter 30, to perform the backup, as it will allow you to create a new backup device for each day.) You can also click Open to open a file if you have a prepared script that you want to enter as a job.
Figure 31-3. The filled-in General tab of the New Job Step dialog box.
Click Parse to check the syntax of your T-SQL steps, and then click the Advanced tab and set the options, as shown in Figure 31-4. On this tab, you can select which action to take upon success and upon failure of the job: quit the job reporting success, quit the job reporting failure, or go to the next step. You can also specify how many times the job should be retried if it does not succeed and the interval between retries. If the job consists of a T-SQL command or script, you can select an output file to which the output of the T-SQL will be reported. You can either append the output to this file each time the job is run or overwrite the output each time. Click View to view the contents of the output file. Select the Append Output To Step History check box to have the job output added to the job history table entry for this job. You can also specify the user who will run the T-SQL.

Figure 31-4. The filled-in Advanced tab of the New Job Step dialog box.
Click Apply and then click OK to return to the Steps tab of the New Job Properties window, where you can define more job steps if you want. Click New to add a new step to follow the existing selected step. To insert a new step to be performed before an existing step, select the existing step that you want the new step to precede, and then click Insert to display the New Job Step dialog box. Enter the information for the step you want to insert. To delete a step, select the step and click Delete; to edit a step, select it and click Edit. You can also move a step in the list by selecting it and clicking the up or down arrow to the right of the label Move Step. The Start Step drop-down list allows you to select which step will be the first one executed in the job. A green flag will appear next to the ID number of the step that is selected to execute first. Click Apply to apply your steps to the job. If any flow logic between multiple steps would cause a step not to execute, SQL Server will display a warning message when you click Apply and will allow you to change the flow logic.
To create a schedule for the job, click the Schedules tab. To find the current time on a server, select the server name in the NOTE: The Current Date/Time On Target Server drop-down list. Now click New Schedule to display the New Job Schedule dialog box, as shown in Figure 31-5. Schedules specify at what times and on which days a job should be executed. Execution can occur one time or regularly. If you want to run a job manually at random times, you do not have to set up a job schedule—you can simply run the job whenever you want to. Fill in the schedule name, MyDB_backup_schedule, set the options in the Schedule Type area (in this case, select Recurring), and select the Enabled check box. (These settings are shown in Figure 31-5.) The Enabled check box has the same function here as it did in the New Job Properties window.

Figure 31-5. The New Job Schedule dialog box.
Because we chose a recurring schedule type, you must set the times and days when you want the job to be performed. To do so, click Change to display the Edit Recurring Job Schedule dialog box. Enter the new times and days, and click OK to return to the New Job Schedule dialog box. (Remember that we want to set up a daily backup that occurs at 11 P.M.)
Click OK in the New Job Schedule dialog box to accept your schedule and return to the New Job Properties window. To delete a schedule, select the schedule name and click Delete. To edit a schedule, select the schedule name and click Edit.
NOTE
You can also create a new alert for this job. Alerts will be covered in detail later in this chapter.
Click the Notifications tab, shown in Figure 31-6. On this tab, you can set up a notification process so that an operator (or specified user) will be notified of the success, failure, or completion of a job. This notification can be sent by e-mail, by pager, or as a message across the network by using the NET SEND command. You can have the job status written to the Windows NT or Windows 2000 event log, and you can even automatically delete the job after it succeeds, fails, or is completed. To configure operator notification, select the E-mail Operator, Page Operator, and Net Send Operator check boxes, as appropriate, and select the operator name in the drop-down list to the right. (See the "Operators" section later in this chapter to learn how to create operators.) Select the condition on which to perform the notification in the rightmost drop-down list. To write the results to the event log or to automatically delete the job when it is completed, select the appropriate check boxes, and then select the condition on which to perform the action in the associated drop-down lists. In this example, select the Write To Windows Application Event Log check box.

Figure 31-6. The Notifications tab of the New Job Properties window.
When you have finished setting options, click Apply to create your job, and then click OK to exit the New Job Properties window and to return to Enterprise Manager.
Click Jobs in the left pane of Enterprise Manager. You will see MyDB_backup_job included in the list of jobs in the right pane.

Creating a New Category To create a new category, expand a server in the left pane of Enterprise Manager, expand the Management folder, right-click Jobs, point to All Tasks in the shortcut menu, and then choose Manage Job Categories. The Job Categories dialog box appears, as shown in Figure 31-7, enabling you to add a new category, view existing categories and the jobs that are in them, and delete categories.

Figure 31-7. The Job Categories dialog box.

Using T-SQL

The T-SQL commands used to create a job, add steps to a job, and create a schedule for a job are the system stored procedures sp_add_job, sp_add_jobstep, and sp_add_jobschedule. These stored procedures have many optional parameters, as shown in the code presented in this section. SQL Server assigns each unspecified parameter a default value. Enterprise Manager is much simpler to use for creating jobs because its GUI guides you through the options for the job, preventing your forgetting anything. With T-SQL, you must either include values for all of the optional parameters or be certain that the default values for any omitted parameters are satisfactory values for your job. You should use Enterprise Manager instead of running these stored procedures by hand. You can then generate the T-SQL scripts that were used by Enterprise Manager to create a job by right-clicking the job name, pointing to All Tasks in the shortcut menu, and then choosing Generate SQL Script. This technique will allow you to re-create the job using the script, if you ever need to.

To run any of the stored procedures just mentioned, you must be using the msdb database because the procedures are stored there. Let's look at the parameters that are available with the stored procedures, in case you do want to use the procedures. All the stored procedures described in this chapter use the same general syntax. The sp_add_job stored procedure's syntax is shown here:

 sp_add_job [@job_name =] 'job_name' [,[@enabled =] enabled] [,[@description =] 'description'] [,[@start_step_id =] step_id] [,[@category_name =] 'category'] [,[@category_id =] category_id] [,[@owner_login_name =] 'login'] [,[@notify_level_eventlog =] eventlog_level] [,[@notify_level_email =] email_level] [,[@notify_level_netsend =] netsend_level] [,[@notify_level_page =] page_level] [,[@notify_email_operator_name =] 'email_name'] [,[@notify_netsend_operator_name =] 'netsend_name'] [,[@notify_page_operator_name =] 'page_name'] [,[@delete_level =] delete_level] [,[@originating_server =] 'server_name' [,[@job_id =] job_id OUTPUT]

The syntax for sp_add_jobstep is shown here:

 sp_add_jobstep [@job_id =] job_id | [@job_name =] 'job_name'] [,[@step_id =] step_id] {,[@step_name =] 'step_name'} [,[@subsystem =] 'subsystem'] [,[@command =] 'command'] [,[@additional_parameters =] 'parameters'] [,[@cmdexec_success_code =] code] [,[@on_success_action =] success_action] [,[@on_success_step_id =] success_step_id] [,[@on_fail_action =] fail_action] [,[@on_fail_step_id =] fail_step_id] [,[@server =] 'server'] [,[@database_name =] 'database'] [,[@database_user_name =] 'user'] [,[@retry_attempts =] retry_attempts] [,[@retry_interval =] retry_interval] [,[@os_run_priority =] run_priority] [,[@output_file_name =] 'file_name'] [,[@flags =] flags]

The syntax for sp_add_jobschedule is shown here:

 sp_add_jobschedule [@job_id =] job_id, | [@job_name =] 'job_name', [@name =] 'name' [,[@enabled =] enabled] [,[@freq_type =] freq_type] [,[@freq_interval =] freq_interval] [,[@freq_subday_type =] freq_subday_type] [,[@freq_subday_interval =] freq_subday_interval] [,[@freq_relative_interval =] freq_relative_interval] [,[@freq_recurrence_factor =] freq_recurrence_factor] [,[@active_start_date =] active_start_date] [,[@active_end_date =] active_end_date] [,[@active_start_time =] active_start_time] [,[@active_end_time =] active_end_time]

MORE INFO
For a description of each parameter and its default value, look up the name of the relevant stored procedure in the Books Online index.

NOTE
The stored procedures described here, as well as all other stored procedures related to creating and managing jobs, operators, notifications, and alerts, are stored in the msdb database. You must be using this database to run these procedures.

Using the Create Job Wizard

Enterprise Manager provides a wizard that guides you through the job creation process in a step-by-step fashion. One limitation of using the wizard is that it restricts you to creating a job that has only one job step. It does, however, allow you to supply a schedule for the job and to specify operators who will be notified of the job's status. You can add more steps to the job after you create it by modifying the job later using Enterprise Manager.

To use the Create Job Wizard to create a job, follow these steps:

Choose Wizards from the Enterprise Manager Tools menu, expand the Management folder in the Select Wizard dialog box that appears, and select Create Job Wizard to display the Create Job Wizard welcome screen, shown in Figure 31-8.
Click Next to display the Select Job Command Type screen, shown in Figure 31-9. This screen lets you specify which type of step you will create for the job. For this example, select Transact-SQL Command.

Figure 31-8. The Create Job Wizard welcome screen.

Figure 31-9. The Select Job Command Type screen.
Click Next to display the Enter Transact-SQL Statement screen (Figure 31-10). For a T-SQL command, you must select the database in which to run the command and then either type the statement or statements for the job or click Open to find and open the file that contains the T-SQL statements you want to use. Click Parse to check the syntax of the T-SQL statements you have entered. If you had selected Operating-System Shell Command or Active Script as the command type, you would be prompted to enter the commands for those types. For this example, type the T-SQL command to back up the master database to a previously created device named backup_master_dev, as shown in Figure 31-10.

Figure 31-10. The Enter Transact-SQL Statement screen.
Click Next to display the Specify Job Schedule screen, shown in Figure 31-11. Here you can specify when the job should be run.
The Now option specifies that the job will be run as soon as the wizard is finished. The other options are self-explanatory. For this example, select On A Recurring Basis, and then select Schedule to set the schedule. The Edit Recurring Job Schedule dialog box appears, as shown in Figure 31-12. Use the options to create the desired schedule, and click OK to accept it and to return to the Specify Job Schedule screen.

Figure 31-11. The Specify Job Schedule screen.

Figure 31-12. The Edit Recurring Job Schedule dialog box.
Click Next to display the Job Notifications screen, shown in Figure 31-13. In the Net Send or E-mail drop-down list or both, select an operator to be notified of the job completion status. You must have already defined the operators for them to appear in the drop-down list. (Figure 31-13 shows no operators defined.) If you want to notify an operator that has not been defined, complete the wizard and then add an operator as described in the section "Operators" later in this chapter. You can then modify the job properties to notify that operator. You can also cancel the wizard, create the operator, and restart the wizard.

Figure 31-13. The Job Notifications screen.
Click Next to display the Completing The Create Job Wizard screen, shown in Figure 31-14. Here you can assign a name for your job by replacing the default name in the Job Name text box—in this example, our job is named Backup_master_job. Check the text in the Description box to be sure it reflects the options you want, and if so, click Finish to create the job. If not, click Back and make the appropriate changes. An informational message box appears if the job was created successfully. Click OK to close this message box.

After you complete the Create Job Wizard, the new job appears in the Jobs folder in Enterprise Manager.

click to view at full size.

Figure 31-14. The Completing The Create Job Wizard screen.

Managing a Job

You can manage and edit your jobs through Enterprise Manager or by using T-SQL. Again, Enterprise Manager might be easier for you to use because you don't have to worry about the syntax and default values involved with T-SQL stored procedures and because the Enterprise Manager GUI guides you through setting the job properties.

Using Enterprise Manager

You can manually start, stop, disable, enable, edit, and create T-SQL scripts for a job by using Enterprise Manager. Instructions for each of these tasks are given here:

To start a job, right-click the job name in the right pane of Enterprise Manager and choose Start Job from the shortcut menu.
To stop a job that is currently running and to cancel any retries that are configured, right-click the job name and choose Stop Job from the shortcut menu.
To disable a job so that it can be tested without allowing it to be performed at its scheduled time, or for any other reason, right-click the job name and choose Disable Job from the shortcut menu. Choose Enable Job to re-enable the job.
To edit a job, a schedule, or any other property of a job, right-click the job name and choose Properties from the shortcut menu to display the job's Properties window, which contains the same four tabs you used to create the job. Make your modifications, click Apply, and then click OK.
To create a T-SQL script for your job in case you want to re-create the job at any time without having to retype the statements, right-click the job name, point to All Tasks in the shortcut menu, and then choose Generate SQL Script to display the Generate SQL Script dialog box. Type a filename, choose a file format (Unicode, ANSI, or OEM text), and click OK.

Using T-SQL

You can also start, stop, enable, disable, and edit a job by using the following T-SQL stored procedures. Remember to use the msdb database when you run these procedures.

sp_start_job Starts the specified job immediately. This procedure requires a job name or job ID number.
sp_stop_job Stops a job that is currently running. This procedure requires a job name, job ID, or master server name.
sp_update_job Allows you to enable, disable, and change the properties of a job. This procedure requires a job name or job ID number.

MORE INFO
To view the syntax of these procedures and the options that can be used with them, look up the specific stored procedure in the Books Online index.

Viewing the Job History

SQL Server maintains a history of job execution information in the sysjobhistory table of the msdb system database. You can view the job history information by using Enterprise Manager or T-SQL.

Using Enterprise Manager

To view the job history by using Enterprise Manager, follow these steps:

Right-click the job name in the right pane of Enterprise Manager and choose View Job History from the shortcut menu to display the Job History dialog box, as shown in Figure 31-15. Here you'll see a line of information describing each execution of the job, any operators notified, and errors or messages received from SQL Server.

Figure 31-15. The Job History dialog box.
To view additional details about job execution status, select the Show Step Details check box in the upper right corner of the dialog box. Figure 31-16 shows the details for the MyDB backup.

Figure 31-16. Step details displayed in the Job History dialog box.
To clear all messages, click Clear All. To refresh the screen to view the status of any new jobs that have been run since you opened the Job History dialog box, click Refresh. To close the Job History dialog box, click Close.

Using T-SQL

To view job history information about scheduled jobs by using T-SQL, run the stored procedure sp_help_jobhistory in the msdb database. The syntax is shown here:

 sp_help_jobhistory [[@job_id =] job_id] [, [@job_name =] 'job_name'] [, [@step_id =] step_id] [, [@sql_message_id =] sql_message_id] [, [@sql_severity =] sql_severity] [, [@start_run_date =] start_run_date] [, [@end_run_date =] end_run_date] [, [@start_run_time =] start_run_time] [, [@end_run_time =] end_run_time] [, [@minimum_run_duration =] minimum_run_duration] [, [@run_status =] run_status] [, [@minimum_retries =] minimum_retries] [, [@oldest_first =] oldest_first] [, [@server =] 'server'] [, [@mode =] 'mode']

If you run this procedure with no parameters or with no job id or job name parameter, information about all scheduled jobs will be returned. The mode parameter specifies whether to return all history information (FULL) or a summary (SUMMARY). The default setting is SUMMARY.

MORE INFO
For details about all other options of this stored procedure, look up "sp_help_jobhistory" in the Books Online index.