Quick Scan Report – Alerts

Alerts for severity levels 19 to 25 and errors 823, 824, and 825 are one of the areas that the Quick Scan Reports reports on.

If you are seeing these messages you should consider creating alerts for these severe error levels on your SQL Server databases.

Alert severity 19 to 25 and errors 823, 824 and 825 are all Fatal errors that you would want to know about as soon as they occur.

Errors 823, 824 and 825 usually indicate problems getting to data in the database files, and can be caused by a number of reasons. With these there is often times a retry that succeeds but the original failed.  Here is an example error 825 email message.

How to Set up Alerts

To set up alerts, you first need an operator configured to receive the notification. You also need to have the SQL Server Agent running for the alerts to send out notifications.

Next expand the SQL Server Agent, then expand Alerts, right click the alerts item and you can add an alert for each of the fat errors that may occur on your SQL Server.

The new alert configuration screens look like this:

First fill out the Alert name, and select the severity.

To configure the notification to go to an operator.

 

And save the alert. Repeat this for the alerts severity 19 to 25, and you will get notified when bad things happen that need your attention.

 

Script to add Operators, Alerts and Notifications

DECLARE @operatorName as NVARCHAR(100);
DECLARE @operatorEmail as NVARCHAR(100);
 
SET @operatorName = N'OPERATOR NAME'; -- REPLACE THIS WITH THE OPERATOR NAME
SET @operatorEmail = N'youremail@yourdomain.com'; -- REPLACE THIS WITH THE OPERATOR EMAIL
 
IF NOT EXISTS(SELECT * FROM msdb..sysoperators WHERE name = @operatorName) 
BEGIN
    EXEC msdb.dbo.sp_add_operator @name=@operatorName, 
            @enabled=1, 
            @weekday_pager_start_time=100, 
            @weekday_pager_end_time=235959, 
            @saturday_pager_start_time=100, 
            @saturday_pager_end_time=235959, 
            @sunday_pager_start_time=100, 
            @sunday_pager_end_time=235959, 
            @pager_days=127, 
            @email_address=@operatorEmail, 
            @category_name=N'[Uncategorized]';
END
 
 
 
EXEC msdb.dbo.sp_add_alert @name=N'ERROR 823', 
		@message_id=823, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000';
		
EXEC msdb.dbo.sp_add_notification @alert_name=N'ERROR 823', 
		  @operator_name=@operatorName , 
            @notification_method = 1;

EXEC msdb.dbo.sp_add_alert @name=N'ERROR 824', 
		@message_id=824, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000';
		
EXEC msdb.dbo.sp_add_notification @alert_name=N'ERROR 824', 
		  @operator_name=@operatorName , 
            @notification_method = 1;

EXEC msdb.dbo.sp_add_alert @name=N'ERROR 825', 
		@message_id=825, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000';
		
EXEC msdb.dbo.sp_add_notification @alert_name=N'ERROR 825', 
		  @operator_name=@operatorName , 
            @notification_method = 1;


 
 
IF NOT EXISTS (SELECT * FROM [msdb].[dbo].[sysalerts] WITH ( NOLOCK ) WHERE [severity] = 19 )
BEGIN
 
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 19 - Fatal Error In Resource', 
            @message_id=0, 
            @severity=19, 
            @enabled=1, 
            @delay_between_responses=900, -- 15 minutes
            @include_event_description_in=1, 
            @job_id=N'00000000-0000-0000-0000-000000000000';
 
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 19 - Fatal Error In Resource', 
            @operator_name=@operatorName , 
            @notification_method = 1;
END
 
IF NOT EXISTS (SELECT * FROM [msdb].[dbo].[sysalerts] WITH ( NOLOCK ) WHERE [severity] = 20 )
BEGIN
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 20 - Fatal Error In Current Process', 
            @message_id=0, 
            @severity=20, 
            @enabled=1, 
            @delay_between_responses=900, -- 15 minutes
            @include_event_description_in=1, 
            @job_id=N'00000000-0000-0000-0000-000000000000';
 
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 20 - Fatal Error In Current Process', 
            @operator_name=@operatorName, 
            @notification_method = 1;
END
 
IF NOT EXISTS (SELECT * FROM [msdb].[dbo].[sysalerts] WITH ( NOLOCK ) WHERE [severity] = 21 )
BEGIN
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 21 - Fatal Error In Database Process', 
            @message_id=0, 
            @severity=21, 
            @enabled=1, 
            @delay_between_responses=900, -- 15 minutes
            @include_event_description_in=1, 
            @job_id=N'00000000-0000-0000-0000-000000000000';
 
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 21 - Fatal Error In Database Process', 
            @operator_name=@operatorName, 
            @notification_method = 1;
END
 
IF NOT EXISTS (SELECT * FROM [msdb].[dbo].[sysalerts] WITH ( NOLOCK ) WHERE [severity] = 22 )
BEGIN
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 22 - Fatal Error Table Integrity Suspect', 
            @message_id=0, 
            @severity=22, 
            @enabled=1, 
            @delay_between_responses=900, -- 15 minutes
            @include_event_description_in=1, 
            @job_id=N'00000000-0000-0000-0000-000000000000';
 
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 22 - Fatal Error Table Integrity Suspect', 
            @operator_name=@operatorName, 
            @notification_method = 1;
END
 
IF NOT EXISTS (SELECT * FROM [msdb].[dbo].[sysalerts] WITH ( NOLOCK ) WHERE [severity] = 23 )
BEGIN
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 23 - Fatal Error Database Integrity Suspect', 
            @message_id=0, 
            @severity=23, 
            @enabled=1, 
            @delay_between_responses=900, -- 15 minutes
            @include_event_description_in=1, 
            @job_id=N'00000000-0000-0000-0000-000000000000';
 
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 23 - Fatal Error Database Integrity Suspect', 
            @operator_name=@operatorName, 
            @notification_method = 1;
END
 
 
IF NOT EXISTS (SELECT * FROM [msdb].[dbo].[sysalerts] WITH ( NOLOCK ) WHERE [severity] = 24 )
BEGIN
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 24 - Hardware Error', 
            @message_id=0, 
            @severity=24, 
            @enabled=1, 
            @delay_between_responses=900, -- 15 minutes
            @include_event_description_in=1, 
            @job_id=N'00000000-0000-0000-0000-000000000000';
 
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 24 - Hardware Error', 
            @operator_name=@operatorName, 
            @notification_method = 1;
END
 
IF NOT EXISTS (SELECT * FROM [msdb].[dbo].[sysalerts] WITH ( NOLOCK ) WHERE [severity] = 25 )
BEGIN
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 25 - System Error', 
            @message_id=0, 
            @severity=25, 
            @enabled=1, 
            @delay_between_responses=900, -- 15 minutes
            @include_event_description_in=1, 
            @job_id=N'00000000-0000-0000-0000-000000000000';
 
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 25 - System Error', 
            @operator_name=@operatorName, 
            @notification_method = 1;
END

 

Need help?

If you need help configuring alerts or anything else with your SQL Server, you can contact me (Steve Stedman) through my SQL Server consulting business Stedman Solutions, LLC.

http://StedmanSolutions.com