{"id":3617,"date":"2021-11-26T08:00:00","date_gmt":"2021-11-26T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3617"},"modified":"2024-03-08T20:46:00","modified_gmt":"2024-03-08T17:46:00","slug":"pos-mporoyme-na-lamvanoyme-email-kathe-fora-2","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-lamvanoyme-email-kathe-fora-2\/","title":{"rendered":"How can we get email whenever an error occurs in SQL Server Error Log"},"content":{"rendered":"

In an instance of SQL Server that potential problems occur are recorded in a place called Error Log<\/strong>. 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. <\/p>\n\n\n\n

The footsteps<\/h2>\n\n\n\n

To begin with, we should have activated it Database Mail<\/strong> in SQL Server. We have seen how this is done in this <\/a>article<\/a>.<\/p>\n\n\n\n

Creating the Operator<\/h4>\n\n\n\n

After making sure that Database Mail is working, we need to build it Operator<\/a>. O Operator <\/em>is the alias of the email that will be the recipient. <\/p>\n\n\n\n

To create it with SQL Server Management Studio we choose:<\/p>\n\n\n\n

Right click on SQL Server Agent, New, Operator\u2026<\/em><\/p>\n\n\n\n

\"\"
01<\/figcaption><\/figure>\n\n\n\n

Then we give it to Name <\/em>a nickname and at E-mail name<\/em> we define the email that will be the recipient:<\/p>\n\n\n\n

\"\"
02<\/figcaption><\/figure>\n\n\n\n

Instead of a GUI we can use T-SQL and create it with the following code:<\/p>\n\n\n\n

sp_configure 'show advanced options', 1;  \nGO  \nRECONFIGURE;  \nGO  \nsp_configure 'Database Mail XPs', 1;  \nGO  \nRECONFIGURE  \nGO\nUSE [msdb]\nGO\n\nEXEC msdb.dbo.sp_add_operator @name=N'sqlserver_alerting', \n\t\t@enabled=1, \n\t\t@weekday_pager_start_time=80000, \n\t\t@weekday_pager_end_time=180000, \n\t\t@saturday_pager_start_time=90000, \n\t\t@saturday_pager_end_time=180000, \n\t\t@sunday_pager_start_time=80000, \n\t\t@sunday_pager_end_time=180000, \n\t\t@pager_days=0, \n\t\t@email_address=N'dataplatform.gr@gmail.com', \n\t\t@category_name=N'[Uncategorized]'\nGO\n<\/pre>\n\n\n\n
Create an Alert<\/h4>\n\n\n\n
In the next step we will create one by one Alerts<\/a> for each error. Errors are divided into categories Severities<\/a> depending on their severity. We want anyone who belongs to Severity 17 and above to send. <\/p>\n\n\n\n
So we go back to SQL Server Management Studio and choose:<\/p>\n\n\n\n
Right click on SQL Server Agent, New, Alert\u2026<\/em><\/p>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
In the tab General <\/em>define a name that refers to the Severity we want and select the corresponding Severity in the drop box:<\/p>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
In the tab Response <\/em>we choose Notify operators<\/em> and click on E-mail<\/em> by Operator <\/em>which we created in the first step:<\/p>\n\n\n\n
\"\"
05<\/figcaption><\/figure>\n\n\n\n
In the tab Options<\/em>, we select it E-mail<\/em> and we define it Delay between responses<\/em> every 5 minutes so that it sends with this periodicity:<\/p>\n\n\n\n
\"\"
06<\/figcaption><\/figure>\n\n\n\n
We repeat the same process for each Severity from 17 to 25.<\/p>\n\n\n\n
After we are done with these, we also add the Errors with a code 823,824,825<\/strong> which although serious (they show us disk corruption) do not belong to any Severity.<\/p>\n\n\n\n
Also if we use Always On Availability Groups<\/strong> we also add the Errors with code 1480, 35264, 35625<\/strong> which will inform us if any change is made between Primary and Secondary, as well as if data transfer between them stops.<\/p>\n\n\n\n
So we do the same process for each Error Number (823,824,825,1480,35264,35625):<\/p>\n\n\n\n
\"\"
07<\/figcaption><\/figure>\n\n\n\n
Instead of a GUI we can use T-SQL and create them with the following code:<\/p>\n\n\n\n
USE [msdb]\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'severity 17', \n\t\t@message_id=0, \n\t\t@severity=17, \n\t\t@enabled=1, \n\t\t@delay_between_responses=300, \n\t\t@include_event_description_in=1, \n\t\t@job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'severity 17', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'severity 18', \n\t\t@message_id=0, \n\t\t@severity=18, \n\t\t@enabled=1, \n\t\t@delay_between_responses=300, \n\t\t@include_event_description_in=1, \n\t\t@job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'severity 18', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'severity 19', \n\t\t@message_id=0, \n\t\t@severity=19, \n\t\t@enabled=1, \n\t\t@delay_between_responses=300, \n\t\t@include_event_description_in=1, \n\t\t@job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'severity 19', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'severity 20', \n\t\t@message_id=0, \n\t\t@severity=20, \n\t\t@enabled=1, \n\t\t@delay_between_responses=300, \n\t\t@include_event_description_in=1, \n\t\t@job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'severity 20', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'severity 21', \n\t\t@message_id=0, \n\t\t@severity=21, \n\t\t@enabled=1, \n\t\t@delay_between_responses=300, \n\t\t@include_event_description_in=1, \n\t\t@job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'severity 21', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'severity 22', \n\t\t@message_id=0, \n\t\t@severity=22, \n\t\t@enabled=1, \n\t\t@delay_between_responses=300, \n\t\t@include_event_description_in=1, \n\t\t@job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'severity 22', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'severity 23', \n\t\t@message_id=0, \n\t\t@severity=23, \n\t\t@enabled=1, \n\t\t@delay_between_responses=300, \n\t\t@include_event_description_in=1, \n\t\t@job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'severity 23', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'severity 24', \n\t\t@message_id=0, \n\t\t@severity=24, \n\t\t@enabled=1, \n\t\t@delay_between_responses=300, \n\t\t@include_event_description_in=1, \n\t\t@job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'severity 24', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'severity 25', \n\t\t@message_id=0, \n\t\t@severity=25, \n\t\t@enabled=1, \n\t\t@delay_between_responses=300, \n\t\t@include_event_description_in=1, \n\t\t@job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'severity 25', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'extra error 823', \n\t\t@message_id=823, \n\t\t@severity=0, \n\t\t@enabled=1, \n\t\t@delay_between_responses=300, \n\t\t@include_event_description_in=1, \n\t\t@job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'extra error 823', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'extra error 824', \n\t\t@message_id=824, \n\t\t@severity=0, \n\t\t@enabled=1, \n\t\t@delay_between_responses=300, \n\t\t@include_event_description_in=1, \n\t\t@job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'extra error 824', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'extra error 825', \n\t\t@message_id=825, \n\t\t@severity=0, \n\t\t@enabled=1, \n\t\t@delay_between_responses=300, \n\t\t@include_event_description_in=1, \n\t\t@job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'extra error 825', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Availability Group - Role Change', \n                                @message_id=1480, \n                                @severity=0, \n                                @enabled=1, \n                                @delay_between_responses=300, \n                                @include_event_description_in=1, \n                                @job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Availability Group - Role Change', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Availability Group - Data Movement Suspended', \n                                @message_id=35264, \n                                @severity=0, \n                                @enabled=1, \n                                @delay_between_responses=300, \n                                @include_event_description_in=1, \n                                @job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Availability Group - Data Movement Suspended', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\nEXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Availability Group - Data Movement Resumed', \n                                @message_id=35265, \n                                @severity=0, \n                                @enabled=1, \n                                @delay_between_responses=300, \n                                @include_event_description_in=1, \n                                @job_id=N'00000000-0000-0000-0000-000000000000'\nGO\nEXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Availability Group - Data Movement Resumed', @operator_name=N'sqlserver_alerting', @notification_method = 1\nGO\n<\/pre>\n\n\n\n
Almost ready<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n
\"\"
08<\/figcaption><\/figure>\n\n\n\n
How do we test that the Alerts work and we will receive an E-mail<\/h2>\n\n\n\n
With the following T-SQL code we can create a fake Error to see that it works:<\/p>\n\n\n\n
Use [master]\nGO\n\nRAISERROR (N'An error occurred Severity 17: insufficient resources !', 17, 1)\nWITH LOG\n \nGO<\/pre>\n\n\n\n
After a while we will see that we will receive an email stating the error as in the image below:<\/p>\n\n\n\n
\"\"
09<\/figcaption><\/figure>\n\n\n\n
Bonus Script (To install email alerting with one click)<\/h2>\n\n\n\n
With the following sql script modifying it Profile <\/strong>who is the sender and the Operator <\/strong>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:<\/p>\n\n\n\n
create_dbmail_alert_alltogether<\/a>Download<\/a><\/div>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n