How can we get email whenever an error occurs in SQL Server Error Log

How can we get email whenever an error occurs in SQL Server Error Log
How can we get email whenever an error occurs in SQL Server Error Log

In an instance of SQL Server that potential problems occur are recorded in a place called Error Log. However, it would be very useful if SQL Server automatically forwarded it to us in an email whenever an important error appeared in the Error Log. In the article we will see in detail how this is done either with a graphical environment or with T-SQL.

The footsteps

To begin with, we should have activated it Database Mail in SQL Server. We have seen how this is done in this article.

Creating the Operator

After making sure that Database Mail is working, we need to build it Operator. O Operator is the alias of the email that will be the recipient.

To create it with SQL Server Management Studio we choose:

Right click on SQL Server Agent, New, Operator…

How can we get email whenever an error occurs in SQL Server Error Log
01

Then we give it to Name a nickname and at E-mail name we define the email that will be the recipient:

How can we get email whenever an error occurs in SQL Server Error Log
02

Instead of a GUI we can use T-SQL and create it with the following code:

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Database Mail XPs', 1;  
GO  
RECONFIGURE  
GO
USE [msdb]
GO

EXEC msdb.dbo.sp_add_operator @name=N'sqlserver_alerting', 
		@enabled=1, 
		@weekday_pager_start_time=80000, 
		@weekday_pager_end_time=180000, 
		@saturday_pager_start_time=90000, 
		@saturday_pager_end_time=180000, 
		@sunday_pager_start_time=80000, 
		@sunday_pager_end_time=180000, 
		@pager_days=0, 
		@email_address=N'dataplatform.gr@gmail.com', 
		@category_name=N'[Uncategorized]'
GO

Create an Alert

In the next step we will create one by one Alerts for each error. Errors are divided into categories Severities depending on their severity. We want anyone who belongs to Severity 17 and above to send.

So we go back to SQL Server Management Studio and choose:

Right click on SQL Server Agent, New, Alert…

How can we get email whenever an error occurs in SQL Server Error Log
03

In the tab General define a name that refers to the Severity we want and select the corresponding Severity in the drop box:

How can we get email whenever an error occurs in SQL Server Error Log
04

In the tab Response we choose Notify operators and click on E-mail by Operator which we created in the first step:

How can we get email whenever an error occurs in SQL Server Error Log
05

In the tab Options, we select it E-mail and we define it Delay between responses every 5 minutes so that it sends with this periodicity:

How can we get email whenever an error occurs in SQL Server Error Log
06

We repeat the same process for each Severity from 17 to 25.

After we are done with these, we also add the Errors with a code 823,824,825 which although serious (they show us disk corruption) do not belong to any Severity.

Also if we use Always On Availability Groups we also add the Errors with code 1480, 35264, 35625 which will inform us if any change is made between Primary and Secondary, as well as if data transfer between them stops.

So we do the same process for each Error Number (823,824,825,1480,35264,35625):

How can we get email whenever an error occurs in SQL Server Error Log
07

Instead of a GUI we can use T-SQL and create them with the following code:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 17', 
		@message_id=0, 
		@severity=17, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 17', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 18', 
		@message_id=0, 
		@severity=18, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 18', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 19', 
		@message_id=0, 
		@severity=19, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 19', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 20', 
		@message_id=0, 
		@severity=20, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 20', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 21', 
		@message_id=0, 
		@severity=21, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 21', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 22', 
		@message_id=0, 
		@severity=22, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 22', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 23', 
		@message_id=0, 
		@severity=23, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 23', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 24', 
		@message_id=0, 
		@severity=24, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 24', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 25', 
		@message_id=0, 
		@severity=25, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 25', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'extra error 823', 
		@message_id=823, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'extra error 823', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'extra error 824', 
		@message_id=824, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'extra error 824', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'extra error 825', 
		@message_id=825, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'extra error 825', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Availability Group - Role Change', 
                                @message_id=1480, 
                                @severity=0, 
                                @enabled=1, 
                                @delay_between_responses=300, 
                                @include_event_description_in=1, 
                                @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Availability Group - Role Change', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Availability Group - Data Movement Suspended', 
                                @message_id=35264, 
                                @severity=0, 
                                @enabled=1, 
                                @delay_between_responses=300, 
                                @include_event_description_in=1, 
                                @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Availability Group - Data Movement Suspended', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Availability Group - Data Movement Resumed', 
                                @message_id=35265, 
                                @severity=0, 
                                @enabled=1, 
                                @delay_between_responses=300, 
                                @include_event_description_in=1, 
                                @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Availability Group - Data Movement Resumed', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO

Almost ready

In the end, through SQL Server Management Studio, under Alerts and Operators of the SQL Server Agent, we should see something similar to the image below:

How can we get email whenever an error occurs in SQL Server Error Log
08

How do we test that the Alerts work and we will receive an E-mail

With the following T-SQL code we can create a fake Error to see that it works:

Use [master]
GO

RAISERROR (N'An error occurred Severity 17: insufficient resources !', 17, 1)
WITH LOG
 
GO

After a while we will see that we will receive an email stating the error as in the image below:

How can we get email whenever an error occurs in SQL Server Error Log
09

Bonus Script (To install email alerting with one click)

With the following sql script modifying it Profile who is the sender and the Operator which is the email masker with the corresponding email, smtp server and credentials, we can create the email profile, email operator and all alerts with one click:

Sources:

Share it

Leave a reply