Configuring Database Mail Notifications in MS SQL Server

Introduction

Often, there is a need to somehow inform administrators about the problems with a server. Notifications are generally divided into 2 types:

1) real-time notifications, i.e. those that must come immediately when a problem occurs

2) delayed notifications, i.e. those that come after a fairly long time (more than 1 hour) after a problem occurs.

In my work, it was necessary to extend the functionality of the regular Database Mail.

In this article, we will consider an example of how to generate notifications in HTML tables and then send them to administrators.

Solution

1. Configure Database Mail

2. Create a table for recipients:

Code

3. Create a table for addresses of the recipients:

Code

4. Create a table for a message queue:

Code

5. Create an archive table for messages sent from the message queue:

Code

This information is needed for the history. Besides, this table needs to be cleared from very old data (for example, older than a month).

6. Create a stored procedure that registers a new message in the message queue:

Code

7. Create a stored procedure that returns a string from the addresses by the code or the primary email address of a recipient:

Code

8. Create the necessary functions for working with dates and time:

Code

9. Create a stored procedure that creates an HTML report on messages in the form of a table:

Code

10. Create a stored procedure that sends messages:

Code

This stored procedure takes each message from the message queue and wraps it in an HTML report in the form of a table. For recipients, based on their code or primary email address, it creates a string consisting of email addresses, to which a message is sent. In this way, all the selected messages are processed. Here, the msdb.dbo.sp_send_dbmail stored procedure is used.

11. Create two tasks in Agent (the first one is for real-time notifications (schedule 1 time per minute), the second one is for simple notifications (schedule 1 time per hour)). Add the following to the code of the task:

Here is an example of error reporting:

Code

Here, the svr.KillFullOldConnect stored procedure is used.

Result

This article includes an example of extending the functionality of a regular Database Mail and an example of how to generate notifications in HTML tables and then email them to administrators. This approach allows notifying administrators about different problems in real time or after a certain time, thereby minimizing the occurrence of a critical problem and failure of DBMS and server, which in turn protects production from workflow delay.

References:

  1. Sp_send_dbmail
  2. Database Mail
  3. Srv.KillFullOldConnect
Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.
Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.