Jobs

One valuable administrative feature of SQL Server is the capability to launch the execution of custom jobs at specified times. Each job has properties such as name, description, schedule, and a list of operators to be notified in case of success, completion, or failure, as well as a list of steps that will be performed as part of the job and actions to be taken after completion of a job. These steps can be defined as Transact-SQL code, Active Script code, or operating system commands.

Administration of Jobs

This section look at the basics of job creation from Management Studio to show the potential of this, but it will not go into too much detail.The following exercise creates a job that performs a backup of the transaction log if it is more than 95 percent full. It is based on the util.ap_LogBackupIfAlmostFull stored procedure.

You can create a job using a wizard or directly from the Management Studio tree:

1. Open Management Studio and expand the local server in Object Explorer.

2. Expand SQL Server Agent. Make sure that it is running (you'll see a green arrow on the icon or you can open Properties on the context-sensitive menu).

3. Right-click Jobs and choose New Job. Management Studio displays a New Job window.

4. Fill in the General tab with the information shown in the following illustration:

   

5. Open the Steps page.

6. Click New to start creating the first step. The application displays the New Job Step dialog box.

7. In the Step Name field, type do backup.

8. Make sure that Transact-SQL Script (T-SQL) is selected in the Type list.

9. Specify Assets as the working database.

10. You can either populate the Command text box with script from the file (using the Open button) or, as in this case, enter code manually for the execution of a stored procedure:

     exec util.ap_LogBackup!fAlmostFull 'Asset5', 95 

11. The dialog box should look like this:

    

12. Click the Advanced page to see other options. You can specify behavior in the case of successful or unsuccessful completion, the log file to record the output of the script, and retry options. For this exercise, accept the default values and close the dialog box. SQL Server returns you to the Steps tab of the New Job window.

13. You will create only one step for this job, so now you can click the Schedules option to set a schedule.

14. Click the New button to display the New Job Schedule dialog box.

15. Name the schedule Every 5 min. The Schedule Type is set to Recurring, but the default frequency is not what you want.

16. Select Daily in Occurs under the Frequency group.

17. Set Daily Frequency to Occurs Every 5 Minute(s), as shown here:

    

18. Click OK to close the dialog box. The application returns you to the New Job window.

19. You can use the Notifications page to set activities that will occur when the job completes. It is possible to page operators or to send e-mail to operators, write the status to the Windows NT application event log, or automatically delete the job. You can also set Alerts and alternate Target server, but for this example leave the default values and press OK.

Note 

SQL Server will execute this job every five minutes from now on. If you want to disable it, you can edit tbe job or just rigbt-click tbe job in Management Studio and select Disable Job from tbe pop-up menu.

All of the functionality in this job is actually provided by the util.ap_LogBackupIfAlmostFull stored procedure.

Note 

Tbe only requirement that such a stored procedure must comply with is that it must return a success status (0 in the case of success; any other number represents an error code). SQL Server Agent uses this value to determine the completion status of the job and potentially execute subsequent steps. Returning a success status is a highly recommended practice when the stored procedure works inside the job.

The util.ap_LogBackupIfAlmostFull stored procedure calls the util.ap_LogBackupIfAlmostFull stored procedure to obtain the amount of log space available in the database. If the limit is reached, it creates a backup device using the sys.sp_addumpdevice system stored procedure and performs a backup of the transaction log:

 alter Procedure util.ap_LogBackup!fAlmostFull --Do backup of transaction log -- if percent of space used is bigger than OfItPercentLimit. -- test: exec util.ap_LogBackup!fAlmostFull 'Assets', 0, 1      (           @chvDbName sysname,           @fItPercentLimit float,           @debug int = 0      ) As set nocount on set xact_abort on declare   @intErrorCode int,           @fltPercentUsed float,           @chvDeviceName sysname,           @chvF i1eName sysname,           @chvSQLPath varchar(8000) -- how much of log space is used at the moment exec util.ap_LogSpacePercentUsed_Get @chvDbName,                                      @fItPercentUsed OUTPUT  -- if limit is not reached, just go out if @fItPercentUsed < @fItPercentLimit      return exec master.dbo.xp_regread        'HKEY_LOCAL_MACHINE'       ,'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer'       ,'BackupDirectory',@chvSQLPath OUTPUT Select @chvDeviceName = @chvDbName                       + Convert (Varchar, GetDate(), 112),        @chvFileName   = @chvSQLPath +  '\'                       + @chvDeviceName                       + '.bkp' if @debug <> 0      select @chvDeviceName chvDeviceName,             @chvFileName chvFileName set xact_abort off begin try    EXEC sys.sp_addumpdevice 'disk', @chvDeviceName, @chvFileName end try begin catch    SELECT @intErrorCode = ERROR_NUMBER(); end catch set xact_abort on -- 15026 - it is OK if dump device already exists if @intErrorCode = 0 or @intErrorCode = 15026     BACKUP LOG ©chvDbName TO ©chvDeviceName return 

Tip 

Some might argue that such a stored procedure and job are not needed in the last couple of versions of SQL Server because it can increase the size of a transaction log automatically if it approaches its specified limit and that this is an academic example. This is true, hut it's valid only if you can afford unlimited storage. If your disk resources are limited, it is a much better solution to clear the log. Alternatively, you can increase the frequency of scheduled transaction log backups.

An Alternative to Job Scheduler

Microsoft has developed Job Scheduler into a relatively sophisticated tool, with these features:

• Steps are included as components of jobs to allow better control.

• You can continue or even stop execution from different points, depending on the success or failure of each step.

• Operators can be notified according to predefined criteria.

• Each step can be coded in a different language (including Transact-SQL, ActiveX Scripts, operating system commands, or commands that call replication and maintenance services and utilities).

In the past, the only way to create a complex job was to code everything in Transact-SQL. Now, simpler jobs can be implemented using steps. If you really need a sophisticated solution, you still need the power of Transact-SQL or ActiveX Script.

SQL Server includes a set of stored procedures and extended stored procedures that can achieve everything that you can do within Job Scheduler. They reside in the master and msdb databases. (The database is used by SQL Server Agent to hold information about jobs, schedules, and operators.)

The following paragraphs will quickly review some of these stored procedures.

Stored Procedures for Maintaining Jobs

The sp_help_job stored procedure returns information about jobs. If no parameters are specified, the stored procedure returns a result set with a list of jobs and their attributes. If the job name (or ID) is specified, the stored procedure returns an additional result set that describes the job's steps, schedules, and target servers.

The sp_add_job, sp_delete_job, and sp_update_job stored procedures are used to create, delete, and change existing jobs, respectively.

The sp_add_jobschedule and sp_add_jobstep stored procedures are designed to associate a schedule and steps with an existing job. Naturally, there are corresponding stored procedures that allow you to delete or update schedules and steps and obtain information about them.

The following example creates a single-step job to perform a backup of the transaction log and assigns a nightly schedule to it:

 USE msdb EXEC sp_add_job @job_name = 'Asset Backup Log',     @enabled = 1,     @description = 'Backup transaction Log of Asset database',     @owner_login_name = 'sa' EXEC sp_add_jobserver @job_name = 'Asset Backup Log',     @server_name = 'DSUNDERIC\ss2k' EXEC sp_add_jobstep @job_name = 'Asset Backup Log',     @step_name = 'Backup Log',     @subsystem = 'TSQL',     @server = 'DSUNDERIC\ss2k5',     @command = ' BACKUP LOG Asset TO bkpAssetLog',     @retry_attempts = 5,     @retry_interval = 5 EXEC sp_add_jobschedule @job_name = 'Asset Backup Log ',     @name = 'Nightly Backup',     @freq_type =4,      -- daily     @freq_interval =1,  -- every 1 day     @active_start_time = '000000' - midnight 

It is much easier to create jobs, schedules, and steps from Management Studio, but the previous script might be useful for deploying a job from a development or test environment into a production environment. Therefore, Microsoft has created a way for you to generate scripts that correspond to your jobs. Just open the context-sensitive menu of a job in Management Studio and choose Script Job | Create To | Query Editor Window. The following script is Management Studio's interpretation of the job that we created two sections ago for managing transaction logs:

 USE [msdb] GO /****** Object: Job [do backups] ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object:  JobCategory [Database Maintenance]  ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories      WHERE name=N'Database Maintenance' AND category_class=l)  BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category           @class=N'JOB',           @type=N'LOCAL',           @name=N'Database Maintenance' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'do backups',       @enabled=l,       @notify_level_eventlog=0,       @notify_level_email=0,       @notify_level_netsend=0,       @notify_level_page=0,       @delete_level=0,       @description=N'No description available.',       @category_name=N'Database Maintenance',       @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object:  Step [Backup if log is 95% full]  ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep       @job_id=@jobld,       @step_name=N'Backup if log is 95% full',       @step_id=l,       @cmdexec_success_code=0,       @on_success_action=l,       @on_success_step_id=0,       @on_fail_action=2,       @on_fail_step_id=0,       @retry_attempts=0,       @retry_interval=0,       @os_run_priority=0, @subsystem=N'TSQL',       @command=N'exec util.ap_LogBackupIfAlmostFull ''Asset5'', 95'       @database_name=N'Assets',       @flags=0 IF (@@ERROR <> 0 OR OReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobld,                                           @start_step_id = 1  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule       @job_id=@jobld,       @name=N'Every 5 min',       @enabled=l,       @freq_type=4,       @freq_interval=l,       @freq_subday_type=4,       @freq_subday_interval=5,       @freq_relative_interval=0,       @freq_recurrence_factor=0,       @active_start_date=20051017,       @active_end_date=99991231,       @active_start_time=0,       @active_end_time=235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver       @job_id = @jobld,       @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback:    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: 

You can use sp_start_Job to instruct SQL Server Agent to run the job immediately, as in the following example:

 USE msdb EXEC sp_start_job @job_name = 'Asset Backup Log' 

There is also an orthogonal stored procedure, sp_stop_job, which is designed to stop execution of a job that is in progress.

Once a job is completed, SQL Server Agent will record its completion status in the job history. You can view the history of a job using sp_help_jobhistory, and you can delete old records from the history using sp_purge_jobhistory.

Operators and Alerts

SQL Server Agent also maintains a list of operators and a list of alerts.

Operators are administrators who should be notified of predefined events configured in SQL Server. The system keeps track of the operator's network, e-mail, and pager addresses, as well as a timetable indicating when the operator is available during the week (and weekends).

Alerts are events that can occur in SQL Server, such as specific errors, errors of a certain severity, and conditions that can occur in a database, as well as the actions that need to be taken to handle the event (such as sending a message to the operator or executing a job).

There is also a third type of object that serves as a link between alerts and operators. Notifications are used to assign and send a message to operator(s) to handle alerts.

Naturally, there are stored procedures to manage these lists of operators and alerts:

• sp_help_operator, sp_add_operator, sp_delete_operator, sp_update_operator

• sp_help_alert, sp_add_alert, sp_delete_alert, sp_update_alert

• sp_help_notification, sp_add_notification, sp_delete_notification, sp_update_notification