This article marks the beginning of a new series that will focus on alerts and errors within the SQL Server Agent subsystem. A pre-defined set of demo Alert templates is included in every Microsoft SQL Server 7.0 installation, and the opportunity exists to create a variety of custom alerts. This initial article in the series will serve as an introduction to alerts and the various errors you might encounter as a database administrator.
So, what is an alert? According to SQL Server Books Online: “…an
alert is a definition that matches one or more SQL Server events and a
response, should those events occur”. In general, an administrator cannot
control the occurrence of events but can control the response to those
events with alerts. Alerts can be defined to respond to SQL Server events
by:
Notifying one or more operators by sending them an e-mail (or a page)
or by notifying them “through the network” (by a net send command);
Forwarding the event to another server;
Executing a correction job to address the problem that has occurred.
These actions are to be taken when specific events occur, such as a specific error,
errors of certain severities or when a database reaches a defined limit of
free space available.
How SQL Alerts Work
All events concerned with Microsoft SQL Server operation are written
to the Microsoft Windows NT Application Log. From time to time, the SQL Server Agent
views this Application Log, watching for originating errors. At the
same time, the SQL Server Agent accesses the system table sysalerts
located in the MSDB database, which contains information about all
alerts defined at the database server level. If the agent discovers
an event that meets the required trigger of an alert, this alert
is then fired.
ATTENTION: The SQL Server pre-defined set of alerts serves only as a template.
By default, SQL Server only counts the number of errors that have occurred; without additional configuration, the server will never
notify operators or execute a job.
ATTENTION: Alerts will not run automatically until the SQL Server
Agent service has been started.
Using Alerts
The use of alerts is necessary for constant control of database server operation and automatic response to non-routine situations, such as:
Errors in the database server configuration (for example, an insufficient number
of locks);
Insufficient database server system resources (for example, lack of free
disk space).
Problems with users’ connections to a database server;
Violation of the structure or integrity of database tables and indexes (common problems with the server’s hardware)
The combined use of jobs and alerts is probably the best and most effective method for handling strife with contingencies. For example, those concerned with a shortage of file space for the transaction log can prevent an emergency by starting a previously created job that will fulfill a truncation of the transaction log. The combination of jobs and alerts also makes for a convenient method of managing mobile users, as they often do not have sufficient preparation for the execution of the administrative tasks on their PC.
