Alerts and Errors

It's always better to know if something goes wrong when it goes wrong, not when someone complains to you about it. SQL Server can be configured to send alert and notifications by email, once you've setup Database Mail.

Alerts can be used to notify an operator or execute a job. SQL Server generates events which enter the Windows Application Log. On startup, SQL Server Agent registers itself as a callback service with the Windows Application Event log. When SQL Server Agent receives a logged event, it compares it to the alerts defined. When a match is found, it fires an alert.

It is important to understand:

• The nature of errors
• The locations errors can occur when T-SQL code is executed
• The data returned by errors
• The severities errors can exhibit
• How to configure the SQL Server error log

Errors raised by the database have the following attributes:

• Error number
• Error message
• Severity - (0-0=information, 11-16=user can correct, 17-19=software errors, 20-24=system errors, 25=SQL Server terminating)
• State – value used to determine the location in code at which the error occurred
• Procedure name – name of stored procedure or trigger in which the error occurred (if applicable)
• Line number – Which line of a batch, stored procedure, trigger or function the error fired

You can create alerts to respond to individual error numbers or to respond to all errors of a specific severity level. It is good practice to fire alerts when errors have a severity greater than 19.

The list of system supplied error messages can be seen:

SELECT * FROM sys.messages
WHERE language_id=1033
ORDER BY message_id

The SQL Server Error Log

A new SQL Server Error Log is created with each restart. Six log files are kept by default. The sys.messages view in msdb shows which system error messages are logged by default. You can control the logging behaviour of individual errors by using the sp_altermessage system stored procedure.

Create an Alert

Alerts are created using the GUI in SSMS or by calling sp_add_alert. When defining an alert, you can specify a SQL Server Agent job that should be started when the alert occurs. Here is an Alert example:

EXEC msdb.dbo.sp_add_alert
@name=’AdventureWorks Transaction Log Full’
@message_id=9002,
@delay_between_responses=0,
@database_name=’AdventureWorks’,
@job_id=’255060-5606-6540-0bbf6fgb50’;
GO

Customised error messages can be created using sp_addmessage, and viewed using RAISERROR.
When an alert fires, two things can be configured to happen:
1) A job can start (only one, though this can start other jobs)
2) An Operator can be notified, using the sp_add_notification procedure:

EXEC msdb.dbo.sp_add_notification
@alert_name=’AdventureWorks Transaction Log Full’,
@operator_name=’SQL Admins’,
@notification_method=1;
GO