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

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How we enable Unified Auditing in Oracle Database - 7 October 2024
• What is PostgreSQL and how do we do a full installation - September 2, 2024
• How do we configure GoldenGate replication to read from Oracle Data Guard Standby - 2 August 2024

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…

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

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…

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

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

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:

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):

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 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:

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:

• Operators
• Create an Operator
• Alerts
• Database Engine Error Severities
• sp_add_operator (Transact-SQL)
• sp_add_alert (Transact-SQL)
• How to Configure SQL Server Agent Alerts