SQL Server Agent

Key summary of SQL Server Agent functionality:

* SQL Server Agent has it's own error log (See SQL Server error logs)

* SQL Server jobs are stored in the MSDB system database

* SQL Server job history is stored in the MSDB system database (sysjobhistory) but can be viewed directly from SQL Server agent rather than querying the table yourself

* SQL Server job steps can be optionally logged to a table in the MSDB system database (the sysjobstepslogs table)

* Don't forget to setup a "fail-safe" operator in the properties of SQL Server Agent

Key Summary of How A SQL Server Job works:

A SQL Server job consists of the following components:

* Steps

* Schedules

* Alerts

* Notifications

* Triggers

Steps

You can optionally log each step. You can log each step to a table. If so, you can execute "sp_help_jobsteplog" to see what was logged. You can also log each step to an output file (a text file).

Schedules

You can setup a schedule specifically for 1 job, or you can share schedules among multiple jobs.

A job can have 2 schedules attached to it.

Alerts

Alerts can be:

* SQL Server event. Either:

* Error number

* Severity level (1-25)

* SQL Server performance condition alert

* WMI event alert

When triggered, an alert can:

* Execute another job

* Notify one or more operators

* Notify a "fail-safe" operator if that is configured. A fail-safe operator is a user who receives the alert if the designated operator cannot be reached.

Note that SQL Server Agent reads from the Windows Application Event log to generate a SQL Server Agent alert. So if your Windows Application Event log is full, no alert will be generated.

If your SQL Server Agent job is executing a stored procedure, you have 2 ways to have the stored procedure trigger a SQL Server Agent alert:

* Use the RAISEERROR command

* Execute the xp_logevent stored procedure

The difference is whether you want the user or client to see the error. RAISEERROR will generate an error seen by the user. xp_logevent will not.

Notifications

Notifications can be:

* Email, page or net send someone if job completes, fails, or succeeds

* Write to the windows application event log if job completes, fails or succeeds

* Automatically delete the job if job completes, fails or succeeds

Note that for each job there can be only 1 operator who gets the email.

Targets

If no target is specified, job runs on local server. Or you can specify multiple servers.