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


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).


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 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 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.


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