AUTOMATING ADMINISTRATION (Page 3)

Jobs, Events and Alerts

The SQL Server 2000 Scheduling engine is run by the SQLServerAgent service. It is very sophisticated and jobs can be specified with multiple inter-dependent steps with multiple complimentary schedules.

Types of job

Microsoft SQL Server 2000 can schedule a wide variety of jobs.

Transact SQL statements.

Any single line of Transact SQL can be scheduled as a Job. This covers almost everything you can execute a stored procedure. Scheduled Backups generate dump commands. Other uses might be to update statistics regularly, drop and re-create indexes and perform DBCC commands.

CmdExec statements

This allows any external program or batch file to be run from SQL Server. This might be used to regularly export data from Microsoft SQL Server 2000 via bcp every day.

Built-in (Replication) jobs

Replication generates a number of pre-defined jobs. LogReader reads the log of the publisher database, the Synch job generates a table script and creates the tables on new subscriptions servers and sends data across with BCP.

ActiveScript jobs

You can also create steps using a scripting language of your choice (VBScript, or JavaScript).

Creating jobs

  • Find and open the Jobs folder under Management SQL Server Agent then right-click Jobs and choose New Jobs
  • In the New Job Properties dialogue box, on the General tab, in the Name field, enter a name for the job

Note: The maximum number of characters is 128.

unnamed-file-229 AUTOMATING ADMINISTRATION
       (Page 3)

  • If you do not want the job to be able to be run immediately following its creation, clear Enabled

For example, if you want to test a job before it is scheduled to run, disable the job.

  • Under Source, select Target local server if the job should run on this server only
  • In the Owner list, click the owner responsible for performing the job

The default owner is the user creating the job. This option is available only to members of the sysadmin role.

  • In the Description box, enter a description of what the job does

Tip: The maximum number of characters is 512.

  • Click the Steps tab, then click New

Each job must have at least one step. A step must be created with the job before the job can be saved.

  • For each step give it a name, specify its type and define its command

image9 AUTOMATING ADMINISTRATION
       (Page 3)

  • If the job is a multi-step job, click the Advanced tab and specify what you want SQL Server to do on success or failure (You might have to create the other steps first)
  • When finished with the step click OK
  • Click the Schedules tab, then click New Schedule
  • In the dialogue box define at least one schedule that the task is to run under

image10 AUTOMATING ADMINISTRATION
       (Page 3)

  • Add other schedules if necessary; when finished click OK
  • Click the Notifications tab and define if any operator is to receive notification of any completion or failure of the job