SQL Server Agent
The SQL Server Agent is a separate process, which is tasked with running database jobs (including jobs setup from a database maintenance plan), raising alerts and notifying operators.
Alerts
Alerts can be created to flag the occurrence of an error, for example a database log becoming full. These can be raised on an individual error number or on a severity level (different types of errors have different severity levels, for example 017 includes all alerts for insufficient resources).
As a minimum alerts should be created to monitor severity levels 019 – 025 for all databases. These include all fatal errors.
It is also advisable to setup an alert to monitor error number 9002 for all databases. This monitors a transaction log becoming full, however it will only fire if the relevant databases transaction log has a restricted growth set. In the response section of this alert a job should be set to perform a transaction log backup (thus truncates the transaction log).
It is also advisable to setup an alert to monitor severity level 017, which includes all alerts for insufficient resources.
All alerts should be configured to notify the relevant operator by email, within their response section.
Operators
A number of operators can be created in order to be emailed and/or paged on the occurrence of a raised alert or job execution. However, in order to be able send these notifications, the NT user that runs the SQL Agent must have a Mail Profile created, e.g. Microsoft Exchange Profile.
Jobs
A job can be created to either run a one off task or schedule a re-occurring process such as a SQL script, a database backup, or a job created via a database maintenance plan.