SQL Server Task Scheduling Via SQL Server Agent

The primary SQL Server task scheduling tool is SQL Server Agent. Although SQL Server Agent runs as a separate Windows service process, users can access the SQL Server Agent service through the SQL Server Database Engine by executing T-SQL commands. The SQL Server Agent process runs on the same computer as SQL Server Database Engine, and there is a strict one-to-one correspondence between SQL Server Database Engine and SQL Server Agent. For example, if you have two instances of SQL Server Database engine running on the same computer, you have two distinct instances of SQL Server Agent running as well. This is a known limitation of the current architecture.

Supported Task Types

What kind of tasks can SQL Server Agent run? The most popular type of task is TSQL scripts. In addition to TSQL, there are several other built-in task execution environments called subsystems. These subsystems include the following functionality:

• Executing operating system commands (CmdExec)

• Running Visual Basic, Java, and Perl scripts (ActiveScripting)

• Executing SQL Server Integration Services packages (SSIS)

• Running XMLA Queries and commands against the Analysis Services engine (ANALYSISQUERY, ANALYSISCOMMAND)

• Executing specialized tasks for Replication (Snapshot, LogReader, Distribution, Merge, QueueReader).

Later in this chapter, during the coverage of proxy accounts, you will find a discussion on the security aspects of running different tasks and their run-time execution credentials. Another important aspect of these task execution environments, the per-subsystem thread pool limit, will be covered as part of the general SQL Server Agent architecture.

Tips and Tricks

In previous releases of SQL Server, all subsystem information was stored in the Registry. In SQL Server 2005, you can get general information about SQL Server Agent subsystems by looking directly at the contents of the msdb.dbo.syssubsystems table, using any SQL Server Database Engine query tool, such as Query Editor or SqlCmd.

All subsystems are loaded upon SQL Server Agent service startup. However, the SSIS subsystem may be deployed or removed later (after SQL Server Agent is already running), as part of a separate installation. SQL Server Agent can handle reconfiguration of this particular subsystem dynamically and does not require a restart.

Warning

Do not attempt to modify the contents of the msdb.dbo.syssubsystems table directly or certain tasks may not function properly. If a particular subsystem fails to load, jobs that have job steps belonging to that subsystem get marked internally as "suspended" and cannot be reset back to normal programmatically. There are two ways to fix the situation if it happens. First, jobs can be deleted and then re-created. Another option is to restart the SQL Server Agent service.

Jobs and Job Steps

SQL Server Agent tasks are called job steps. They are executed as part of a larger entity called a job. A single job can contain multiple job steps of any supported type already described. Most jobs are scheduled to run regularly via the SQL Server Agent scheduling mechanism.

Tips and Tricks

In previous releases of SQL Server, the job schedule was tied directly to a specific job and could never be reused. In SQL Server 2005, a user can create a schedule and then link multiple jobs to it. However, other users cannot link to a shared job schedule for security reasons.

In addition to running on a schedule, jobs can started by direct TSQL commands through use of the sp_start_job stored procedure, they can be triggered by an alert, instructed to run during CPU idle cycles, or they can be directed to run by a master server in a MSX/TSX (master server/target server) distributed execution environment.

Conceptually, jobs, job steps, and job schedules are connected together, as shown in Figure 8-1.

Figure 8-1. SQL Server Agent job conceptual diagram.

As you can see from the diagram, a single job can consist of multiple job steps, can execute on multiple target servers, and can be linked to multiple schedules.

Creating Jobs

The Jobs folder appears under the SQL Server Agent node in the tree view of SQL Server Management Studio (the Object Explorer). By expanding the Jobs folder, you can see a list of all jobs currently configured on your server as shown in Figure 8-2.

Figure 8-2. Object Explorer view of the Jobs folder.

Suppose you want to create a job that automatically rebuilds an index on one of your tables every night. We will walk through this scenario step by step to demonstrate the process of creating a simple job through the graphical user interface. Later we examine the stored procedure calls that the graphical tool calls behind the scenes to run SQL Server Agent.

Right-click the Jobs folder to bring up the New Job option. Select New Job, and you will see the dialog for creating new jobs. On the General page (see Figure 8-3) fill in the job name and a brief description of the job functionality. Although providing the job description is optional, a good set of comments will help others understand your intentions later on and cut down on other users contacting you unnecessarily. By default, the Owner field will show all currently logged on users. As the system administrator, you can access more options by clicking the Browse button (...) on the right. Leave the default value for job category since it is used only to group different jobs for display purposes, but does not effect how a job is executed. After filling in job information, click on Steps in the Select a page tree view on the left to create at least one job step for the job.

Figure 8-3. New Job creation dialoggeneral page.

Creating Job Steps

After selecting Steps you will see the New Job Step dialog (see Figure 8-4). Start creating job steps by giving the first step a name. The job step name should be descriptive to help you distinguish between other steps defined as part of the job. Next, choose Transact-SQL script (T-SQL) as the job step subsystem type. This determines how your job step command is executed by SQL Server Agent. Leave the Run as field blank for now. This field is discussed later as part of the proxy accounts topic. Make sure you select the correct database in which your step is to execute, or the step will fail unless all objects used in your script contain the full name resolution path. Notice how GO is used as a batch separator to split different parts of the script. SQL Server Agent respects this batch separator and issues a separate query against SQL Server Database Engine for each batch. Keep in mind that the job step creation dialog does not perform any token colorization the way Query Editor does. It is a known limitation of the tool, so if you are developing any advanced scripts you may want to use Query Editor first and then cut and paste your query into this dialog after you are finished.

Figure 8-4. New Job Step creation dialog.

Another thing worth mentioning is the use of a well-known job step token: $(DATE). At runtime, SQL Server Agent dynamically evaluates and substitutes a real value for the token. For a list of recognized SQL Server Agent tokens see Table 8-1.

Table 8-1. Reserved SQL Server Agent Tokens

Token	Description
$(A-DBN)	Database name.
$(A-SVR)	Server name.
$(A-ERR)	Error number (used by alerting engine).
$(A-SEV)	Error severity (used by alerting engine).
$(A-MSG)	Message text (used by alerting engine).
$(DATE)	Current date (in YYYYMMDD format).
$(INST)	Instance name. For a default instance, this token is empty.
$(JOBID)	Job ID.
$(MACH)	Computer name.
$(MSSA)	Master SQLServerAgent service name.
$(OSCMD)	Prefix for the program used to run CmdExec job steps.
$(SQLDIR)	The directory in which SQL Server is installed. By default, this value is C:\Program Files\Microsoft SQL Server\MSSQL.
$(STEPCT)	A count of the number of times this step has executed (excluding retries). Can be used by the step command to force termination of a multistep loop.
$(STEPID)	Step ID.
$(SRVR)	Name of the computer running SQL Server. If the SQL Server instance is a named instance, this includes the instance name.
$(TIME)	Current time (in HHMMSS format).
$(STRTTM)	The time (in HHMMSS format) at which the job began executing.
$(STRTDT)	The date (in YYYYMMDD format) on which the job began executing.
$(WMI(property))	Value of specified WMI property retrieved by alerting engine.

Tips and Tricks

The token pattern syntax changed in SQL Server 2005. Previously, tokens had square brackets rather than dollar signs and parentheses. This change is handled dynamically as part of a SQL Server Database Engine upgrade through the MSDB schema update. You should be careful, however, to always use new syntax for new job step definitions.

There were several reasons for this change. First, because it is valid T-SQL to use square brackets to delimit identifier name, SQL Server Agent would have an internal conflict trying to resolve a token that had the same name as the real object name in the script. Second, SQL Server Agent started using the unified Batch Parser component widely adopted across the entire tools suite (SqlCmd, QueryEditor, SMO). This meant syntax needed to be consistent as well.

At this point the necessary job step details have been defined for this scenario. It's time to add a schedule for running this job. You do so by clicking Schedule in the tree view on the left.

Creating Job Schedules

Although it would have been possible to use another shared schedule already defined on the system, assume there is not an appropriate schedule, so that it's necessary to create a new schedule. Follow the user interface as shown in Figure 8-5 to create this schedule.

Figure 8-5. New Job Schedule creation dialog.

This schedule should be recurring, executing daily at 2:00 AM. Now it's necessary to decide whether it will execute forever or have some finite end date.

With schedule creation over, it's time to finish the job creation process. Before that, you might want to ask SQL Server Management Studio to script the entire job creation process to a new Query Editor window by selecting the Script menu option. The following is the approximate script that gets created and split into logical pieces.

The first stored procedure call creates a SQL Server Agent job object and obtains a job identifier to report to the caller.

[View full width]
DECLARE @jobId BINARY(16) EXEC  msdb.dbo.sp_add_job @job_name=N'RebuildingIndexJob',             @enabled=1,             @notify_level_eventlog=0,             @notify_level_email=2,             @notify_level_netsend=2,             @notify_level_page=2,             @delete_level=0,             @description=N'This job will rebuild all indexes of AdventureWorks.Production .Product table',             @category_name=N'[Uncategorized (Local)]',             @owner_login_name=N'REDMOND\dimason',             @job_id = @jobId OUTPUT select @jobId 

Then the job gets associated with the job server that is going to run it.

EXEC msdb.dbo.sp_add_jobserver @job_name=N'RebuildingIndexJob', @server_name = N'KOMODO_P6'

The next thing is to add a job step to the job providing all necessary execution details.

EXEC msdb.dbo.sp_add_jobstep @job_name=N'RebuildingIndexJob',             @step_name=N'TSQL Job step for rebuilding indexes on the table',             @step_id=1,             @cmdexec_success_code=0,             @on_success_action=1,             @on_fail_action=2,             @retry_attempts=0,             @retry_interval=0,             @os_run_priority=0, @subsystem=N'TSQL',             @command=N'ALTER INDEX ALL ON Production.Product REBUILD WITH (ONLINE = ON); GO PRINT ''REBUILD IS SUCCESSFUL. EXECUTED $(DATE)''',             @database_name=N'AdventureWorks',             @flags=0 

The next stored procedure call is somewhat redundant and only serves the purpose of identifying the step number from which to start execution.

[View full width]
EXEC msdb.dbo.sp_update_job @job_name=N'RebuildingIndexJob',             @enabled=1,             @start_step_id=1,             @notify_level_eventlog=0,             @notify_level_email=2,             @notify_level_netsend=2,             @notify_level_page=2,             @delete_level=0,             @description=N'This job will rebuild all indexes of AdventureWorks.Production .Product table every night',             @category_name=N'[Uncategorized (Local)]',             @owner_login_name=N'REDMOND\dimason',             @notify_email_operator_name=N'',             @notify_netsend_operator_name=N'',             @notify_page_operator_name=N'' 

Finally, the job schedule gets created. Even though we called a legacy-style stored procedure, sp_add_jobschedule, rather than explicitly created a schedule through sp_add_schedule, the schedule still gets created behind the scenes and can be reused later.

DECLARE @schedule_id int EXEC msdb.dbo.sp_add_jobschedule @job_name=N'RebuildingIndexJob',             @name=N'Nightly Schedule for rebuilding indexes',             @enabled=1,             @freq_type=4,             @freq_interval=1,             @freq_subday_type=1,             @freq_subday_interval=0,             @freq_relative_interval=0,             @freq_recurrence_factor=1,             @active_start_date=20060101,             @active_end_date=99991231,             @active_start_time=20000,             @active_end_time=235959,             @schedule_id = @schedule_id OUTPUT select @schedule_id 

Almost any operation with SQL Server Agent metadata can be translated the same way into a series of simple stored procedure calls.

The scenario job just created is not very resource consuming, so it is safe to run it at least once in real time to make sure it executes successfully. You can run the job from the Object Explorer tree by right-clicking the newly created job and selecting Start job.