How can we get email whenever an error occurs in SQL Server Error Log
- How do we find what permissions a user has on an Oracle database - 1 November 2024
- 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
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