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