Week 6: Chapter 6 Agenda


Nov 5, 2013 (3 years and 8 months ago)


Week 6: Chapter 6 Agenda

Automation of SQL Server tasks using:

SQL Server Agent


Scripting Technologies

Benefits of Automation

Saves time

Reduces chance of human error
(forgetting , running wrong task, …)

Allows easy execution of tasks in non
peak times or at scheduled times

SQL Server Agent

SQL Server’s primary tool for automation
and scheduling

A service in the control panel of the SQL
Server2000 machine

Configured with Enterprise Manager in
Management object

Can automate many tasks using

SQL statements

Operating System Commands


Configuring SQL Server Agent

SQL Server Agent must be running at all
times. To do this:

Using Service Manager, configure SQL
Server Agent to auto start

Using Enterprise Manager, configure
SQL Server and SQL Server Agent
service to restart automatically if these
services stops unexpectedly

Configuring SQL Server Agent (ctd)

SQL Server Agent logon Account must be
mapped to sysadmin role

Map this account to administrators local

Use a windows domain user account
logon account

Use windows authentication mode for SQL
Server Agent to permit greater flexibility

SQL Server Agent Objects

SQL Server agent works with following


series of tasks


names and contact info


response to conditions
(normally for errors)


A job is a set of instructions, schedules and

Each individual operation is called a step

Step types include Transact
commands, O/S commands, Scripts

Scripts can use VBScript, JavaScript, etc

Jobs can use conditional logic

Notification methods include email, email
pager and Windows NET SEND

Create a Job

Ensure that job is enabled (jobs are
enabled by default)

Specify the owner who is responsible for
performing the job (by default the owner
is the logon account creating the job)

Define whether the job is to execute on a
local server or on multiple remote servers

Job categories help to organize, filter and
manage many


Define job step using T

Identify database to be used

Include required variable and parameters

Can send a result set of a job step to an
output file (output files are often used in
troubleshooting to capture an error message
that may have occurred while the statement
is executing)

Define a job step using OS commands

Operating System commands are
identified by .exe, .bat, .cmd, .com files

Identify a process exit code to indicate
that process was successful

Include the full path to the executable
application in the command text box.

Defining Job Steps

Can use ActiveX Script with languages such
as Microsoft VBScript, Microsoft Jscript etc.

Must identify scripting language in which the
job step is written

Write or Open Active Script (use the SQL
ActiveScriptHost object to print output to the
job step history or create object)

Job Flow Logic Example

JOB 1: Data Transfer

Job Step 1: Back Up Database

Type: Transact

Retry attempts: 1

Job Step2: Transfer Data

Type: CmdExec;

Retry attempts : 2

Job Step3: Custom Application

Type: Active Scripting:

Retry attempts: 0

Write to


Application Log

Notify Operator









Notify Operator


Job Scheduling

After job has been defined you can schedule
the job to be executed automatically

A schedule must be enabled to permit
automated execution

Can use Enterprise Manager (GUI) or
execute sp_add_jobschedule stored
procedure to define job schedule

Job schedules are stored in the
msdb.sysjobschedules system tables

Job Scheduling (ctd)

Can schedule jobs to start automatically:

When SQL Server Agent started

At a specific time (one time only)

On a recurring basis (daily, weekly, or

When CPU is idle


Operators are notified of events that take
place on SQL Server

Operators don’t require a SQL Server login

Notifications are messages sent to the

Notification methods can be:


Email pager

Windows NET SEND command

Operators should be available for contact as


Use group email alias to notify more than one
individual to respond to notification

Test each notification method to ensure that
the operator is able to receive messages

Should specify a work schedule for each
operator to be notified by pager

Use NET SEND command to send messages
to network operators and servers running
Windows 2000 or Windows NT.

Operator Notification

Ensure operator is available to receive

Ensure messenger service is running on
computer of the operator to be notified by NET
SEND command

Review most recent notification steps to
determine date and time of last notification

Test individual notification methods outside of
SQL Server by verifying that you can send e
mail messages, page an operator or
successfully execute a NET SEND command.

Job History

Job history can be viewed from Enterprise
Manager and directly in the
msdb..sysjobhistory system table

Sysjobhistory table records:

Date and time each job step occurred

Success or failure of job step

The operator notified and notification

The duration of job step

Errors or messages from job step

Job History Size

By default, msdb database file properties are
set to auto growth, and the Truncate log on
checkpoint database option is turned on.

By default, maximum job history size is set
at 1,000 rows

By default, maximum job history size for
each job is set at 100 rows

Rows are removed from sysjobhistory
system table in a first
in, first
out (FIFO)
manner when size limit is reached

Execution Permissions

For T
SQL Jobs:

context of job owner or a specific user

Operating System and ActiveX Script jobs:

Members of Sysadmin role use the SQL
Server Agent login account

Job owners that are not members of the
sysadmin role use defined domain user
account called a proxy account