SQL Server Agent – Introduction to Alerts

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

  •  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

Alexzander Nepomnjashiy
Alexzander Nepomnjashiy
I am a Microsoft SQL Server Database Designer for Neo-Systems North-West - a security services, consulting, and training company. I have over eight years of experience in the IT field. I am currently working on several projects which involve the deployment of Microsoft Windows NT Server/Microsoft SQL Server within an enterprise business/financial environment. My typical role in these projects includes extending and improving our clients' corporate ERP systems to manage retail sales data, predict market changes and calculate trends for future market situations (DSS, OLAP). Also among my responsibilities are the design and administration of Microsoft SQL Server 7.0/2000 databases. I am available to work on a contract basis for the following types of projects: - Technical authoring, including books, articles, and white papers; - Network and systems design and analysis; - Database and software development and analysis; - Short-term consulting projects. I hope you find these articles useful. If you have any ideas for future articles (in a field of Microsoft SQL Server databases design, administration, performance optimization), or if you have anything to say about the ones below, please do not hesitate to contact me! Feel free to forward these articles to all interested associates. Thank You!
Previous article
Next article
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.

Latest Articles