Alerts in SLQ Server

The information about execution of jobs and system error messages is stored in the Windows Application log. SQL Server Agent reads this log and compares the stored messages with the alerts defined for the system. If there is a match, SQL Server Agent fires the alert. Therefore, alerts can be used to respond to potential problems (such as filling up the transaction log), different system errors, or user-defined errors. Before explaining how you create alerts, this section discusses system error messages and two logs, the SQL Server Agent error log and the Windows Application log, which are used to capture all system messages (and thus most of the errors).

1. Error Messages

System errors are grouped in four different groups. The Database Engine provides extensive information about each error. The information is structured and includes the following:

  • A unique error message number
  • An additional number between 0 and 25, which represents the error’s severity level
  • A line number, which identifies the line where the error occurred
  • The error text

NOTE The error text not only describes the detected error but also may recommend how to resolve the problem, which can be very helpful to the user.

Example 17.1 queries a nonexistent table in the sample database, thus showing the error message number, the level number, and the corresponding error text.

Example 17.1

USE sample;

SELECT * FROM authors;

The result is

Msg 208, Level 16, State 1, Line 2

Invalid object name ‘authors’.

To view the information concerning error messages, use the sys.messages catalog view. The three most important columns of this view are message_id, severity, and text.

Each unique error number has a corresponding error message. (The error message is stored in the text column, and the corresponding error number is stored in the message_id column of the sys.messages catalog view.) In Example 17.1, the message concerning the nonexistent or incorrectly spelled database object corresponds to error number -208.

The severity level of an error (the severity column of the sys.messages catalog view) is represented in the form of a number between 0 and 25. The levels between 0 and 10 are simply informational messages, where nothing needs to be fixed. All levels from 11 through 16 indicate different program errors and can be resolved by the user. The values 17 and 18 indicate software and hardware errors that generally do not terminate the running process. All errors with a severity level of 19 or greater are fatal system errors. The connection of the program generating such an error is closed, and its process will then be removed.

The messages relating to program errors (that is, the levels between 11 and 16) are shown on the screen only. All system errors (errors with a severity level of 19 or greater) will also be written to the log.

System error messages are written to the SQL Server Agent error log and to the Windows Application log. The following two sections describe these two components.

2. SQL Server Agent Error Log

SQL Server Agent creates an error log that records warnings and errors by default. The following warnings and errors are displayed in the log:

  • Warning messages that provide information about potential problems
  • Error messages that usually require intervention by a system administrator

The system maintains up to ten SQL Server Agent error logs. The current log is called Current, while all other logs have an extension that indicates the relative age of the log. For example, Archive #1 indicates the newest archived error log.

The SQL Server Agent error log is an important source of information for the system administrator. With it, he or she can trace the progress of the system and determine which corrective actions to take.

To view the SQL Server Agent error logs from Management Studio, expand the instance in Object Explorer, expand SQL Server Agent, and expand Error Logs. Double-click one of the files to view the desired log. The log details appear in the details pane of the Log File Viewer dialog box.

3. Windows Application Log

The Database Engine also writes system messages to the Windows Application log. The Windows Application log is the location of all operating system messages for the Windows operating systems, and it is where all application messages are stored. You can view the Windows Application log using the Event Viewer.

NOTE The Windows Application log is also called the Windows Application Event log.

Viewing errors in the Windows Application log has some advantages compared to viewing them in the SQL Server Agent error log. The most important is that the Windows Application log provides an additional component for the search for desired strings.

To view information stored in the Windows Application log, choose Start | Control Panel | Administrative Tools | Event Viewer. In the Event Viewer window, you can choose between system, security, and application messages. For database system messages, click Application. Database system events are identified by the entry MSSQLSERVER in the source column.

4. Defining Alerts to Handle Errors

An alert can be defined to raise a response to a particular error number or to the group of errors that belongs to a specific severity code. Furthermore, the definition of an alert for a particular error is different for system errors and user-defined errors. (The creation of alerts on user-defined errors is described later in this chapter.)

The rest of this section shows how you can create alerts using Management Studio.

4.1. Creating Alerts on System Errors

Example 13.8 (see Chapter 13), in which one transaction was deadlocked by another transaction, will be used to show how to create an alert about a system error number. If a transaction is deadlocked by another transaction, the “victim” must be executed again. This can be done, among other ways, by using an alert.

To create the deadlock (or any other) alert, expand SQL Server Agent, right-click Alerts, and choose New Alert. In the New Alert dialog box (see Figure 17-7), enter the name of the alert in the Name box, choose SQL Server Event Alert in the Type drop-down list, and choose <all databases> from the Database Name drop-down list. Click the Error Number radio button, and enter 1205. (This error number indicates a deadlock problem, where the current process was selected as the “victim”)

The second step defines the response for the alert. In the same dialog box, click the Response page. First check Execute Job, and then choose the job to execute when the alert occurs (backup_sample, in our case). Check Notify Operators, and then, in the Operator List pane, select operators and choose the methods of their notifications (e-mail name, and/or pager e-mail name).

4.2. Creating Alerts on Error Severity Levels

You can also define an alert that will raise a response on error severity levels. As you already know, each system error has a corresponding severity level that is a number between 0 and 25. The higher the severity level is, the more serious the error. Errors with severity levels 20 through 25 are fatal errors. Errors with severity levels 19 through 25 are written to the Windows Application log.

NOTE Always define an operator to be notified when a fatal error occurs.

As an example of how you can create alerts in relation to severity levels, here’s how you use Management Studio to create the particular alert for severity level 25. First, expand SQL Server Agent, right-click Alerts, and choose New Alert. In the Name box, enter a name for this alert (for example, Severity 25 errors). In the Type drop-down list, choose SQL Server Event Alert. In the Database Name drop-down list, choose the sample database. Click the Severity radio button and choose 025 – Fatal Error.

On the Response page, enter one or more operators to be notified via e-mail and/or pager, when an error of severity level 25 occurs.

4.3. Creating Alerts on User-Defined Errors

In addition to creating alerts on system errors, you can create alerts on customized error messages for individual database applications. Using such messages (and alerts), you can define solutions to problems that might occur in an application.

The following steps are necessary if you want to create an alert on a user-defined message:

  1. Create the error message.
  2. Raise the error from a database application.
  3. Define an alert on the error message.

An example is the best way to illustrate the creation of such an alert. We will use the sales table (see Example 5.24 in Chapter 5) and define an alert that fires when the value of the shipping date (the ship_date column) is earlier than the order date (the order_date column).

NOTE Only the first two steps are described here, because an alert on a user-defined message is defined similarly to an alert on a system error message.

Creating an Error Message To create a user-defined error message, you can use either Management Studio or the sp_addmessage stored procedure. Example 17.2 creates the error message for the example using the sp_addmessage stored procedure.

Example 17.2

sp_addmessage @msgnum=50010, @severity=16,

@msgtext=’The shipping date of a product is earlier than the order date’,

@lang=’us_english’, @with_log=’true’

The sp_addmessage system stored procedure in Example 17.2 creates a user-defined error message with error number 50010 (the @msgnum parameter) and severity level 16 (the @severity parameter). All user-defined error messages are stored in the sysmessages system table of the master database and can be viewed by using the sys.messages catalog view. The error number in Example 17.2 is 50010 because all user-defined errors must be greater than 50000. (All error message numbers less than 50000 are reserved for the system.)

For each user-defined error message, you can optionally use the @lang parameter to specify the language in which the message is displayed. This specification may be necessary if multiple languages are installed on your computer. (When the @lang parameter is omitted, the session language is the default language.)

By default, user-defined messages are not written to the Windows Application log. On the other hand, you must write the message to this log if you want to raise an alert on it. If you set the @with_log parameter of the sp_addmessage system procedure to TRUE, the message will be written to the log.

Raising an Error Using Triggers To raise an error from a database application, you invoke the RAISERROR statement. This statement returns a user-defined error message and sets a system flag in the @@error global variable. (You can also handle error messages using TRY/CATCH blocks.)

Example 17.3 creates the trigger t_date_comp, which returns a user-defined error of 50010 if the shipping date of a product is earlier than the order date.

NOTE To execute Example 17.3, the sales table must exist.

Example 17.3

USE sample;

GO

CREATE TRIGGER t_date_comp

ON sales FOR INSERT AS

DECLARE @order_date DATE

DECLARE @shipped_date DATE

SELECT @order_date=order_date, @shipped_date=ship_date FROM INSERTED

IF @order_date > @shipped_date

RAISERROR (50010, 16,-1)

Now, if you insert the following row in the sales table, the shipping date of a product is earlier than the order date:

INSERT INTO sales VALUES (1, 01.01.2017′, ‘01.01.2016’)

The system will return the user-defined error message:

Msg 50010, Level 16, State 1, Procedure t_date_comp, Line 8

The shipping date of a product is earlier than the order date.

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.

Leave a Reply

Your email address will not be published. Required fields are marked *