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).
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.
Example 17.1
USE sample;
SELECT * FROM authors;Code language: SQL (Structured Query Language) (sql)The result is
Msg 208, Level 16, State 1, Line 2
Invalid object name 'authors'.Code language: SQL (Structured Query Language) (sql)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 and 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.
In order to resolve an error, you usually need to read the detailed description of the corresponding error. You can also find detailed error descriptions in Books Online.
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.
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 SQL Server Management Studio, expand the instance in Object Explorer, expand SQL Server Agent, and expand Error Logs. 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.
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.
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 SQL Server system messages, click Application. SQL Server events are identified by the entry MSSQLSERVER.
