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

Page 2: Pre-defined Alerts and Their Triggering Errors