SQL Server Email Alerts Notifications

As  DBAs, we are often required to set up alert notification
emails for various tasks. For example, there might be a requirement to set up
notification alerts if a particular backup job fails. A while back, I  came across the situation
where I was asked to write a script which sent an email alert to a
particular group of users if there was an issue with the Transactional Replication.
The users were not happy with the alert message generated by the pre-defined
alerts introduced by Microsoft in SQL Server Management Studio since they were not able to understand
the description of the message – since the body as well as the heading of the email
were not easily interpreted. After gathering the requirements, I decided to implement
a more functional email alerts system.

Prerequisites:

  • SQL Server 2005(Except Express Edition) or Above
  • A valid database mail profile.

Approach:

I identified that  a table named sysreplicationalerts
which is present in the msdb database and  contains the entire replication
failure alert message. There are around 14 types of alert messages generated for
Replication:

  • Replication Warning: long merge over dial up connection
  • Replication Warning: long merge over LAN connection
  • Replication Warning: Slow merge over dial up connection
  • Replication Warning: Slow merge over LAN connection
  • Replication Warning: Subscription expiration
  • Replication Warning: Transactional Replication Latency
  • Replication: agent custom shutdown
  • Replication: agent failure
  • Replication: agent retried
  • Replication: agent success
  • Replication: expired subscription dropped
  • Replication: Subscriber has failed data validation
  • Replication: Subscriber has passed data validation
  • Replication: Subscription reinitialized after validation
    failure

STEP 1

Create a table named notification_subscribers which
contains all the subscriber information i.e subscriber ip address, server
name, and general name if there is any for that server.

Script for the same is as shown below:

create
table notification_plants
(
row_id
int,
plant_server_ip
varchar(100),
plant_server_name
varchar(100),
general_name
varchar(1000)
)

Then populate the table with the respective server ip, server
name of the respective subscribers as well as if there is any general name. By
general name,  consider for example if a subscriber has been
created for Newyork location, then in the general name we shall include
Newyork.

STEP 2

This step involves creation of a stored procedure named usp_send_replication_alerts
which contains the required logic.

First, I
created a table named replicationalerts and subscriber_alert_message.
I then populated the temporary table named replication_alerts with the
data from sysreplication table. This table is present in the msdb
database of the SQL Server.

create
table #replicationalerts

(
replication_alert_id
int,
[time]
datetime,
subscriber
varchar(100),
alert_error_message
varchar(1000)
)

Insert
#replicationalerts
select
row_number() over(order by alert_id),[time],subscriber,alert_error_text

from
sysreplicationalerts

where
datepart(yyyy,[time]) = datepart(yyyy,getdate())
and
datepart(mm,[time]) = datepart(mm,getdate())
and
datepart(dd,[time]) = datepart(dd,getdate())

When
populating the data, bear in mind that only the current date’s data
should be populated. i.e If the script is executed on 23 September 2010 then
only those alerts encountered on 23 September 2010 should be inserted into the
temporary table.

set
@i = 1
select
@max_count = max(replication_alert_id) from #replicationalerts

while(@i<=@max_count)
begin

select

@subscribername=subscriber
from #replicationalerts where replication_alert_id = @i

select

@patindexname=patindex(‘%%’,@subscribername)

select

@count
= @patindexname-1

select

@left_subscriber_name
= left(@subscribername,@count) from #replicationalerts

select

@alerterrormessage
= alert_error_message from #replicationalerts where replication_alert_id = @i

select

@alert_message_patindexname
= patindex(‘%.%’,@alerterrormessage)

select

@count_alert_message
= @alert_message_patindexname -1

select

@length_alert_message
= len(alert_error_message) from #replicationalerts where replication_alert_id =
@i

select

@final_count
= @length_alert_message-@count_alert_message

select

@left_alert_error_message
= right(@alerterrormessage,@final_count-1) from #replicationalerts

select

@time
= [time] from #replicationalerts where replication_alert_id = @i

insert
#subscriber_alert_message

select
@left_subscriber_name,NULL,@time,@left_alert_error_message

update
#subscriber_alert_message

set
general_name = a.general_name from notification_plants a inner join
#subscriber_alert_message b

on
a.plant_server_name = b.subscriber_name
set
@i = @i+1

end

First the value
of the variable @i is initialized as 1. Then we determined the max value of the
replication_alert_id column present in the temporary table named replicationalerts.
While the value of @i is less than or equal to @max_count  the WHILE loop
continues and fetches the required information as shown above.

Then a
temporary table named #alert_message is created which fetches the data from the
temporary table named subscriber_alert_message. It fetches only those data
which is between 1 hour.

create
table #alert_message

(
row_id
int,
subscriber_name
varchar(1000),
general_name
varchar(1000),
alert_message
varchar(1000)
)

insert
#alert_message

select

rank()
over(order by subscriber_name,alert_message),
subscriber_name,

general_name,

alert_message

from

#subscriber_alert_message

where

datediff(mi,[time],getdate())<=60

The initialized
the value of the variable @j as 1 and the WHILE loop continues till the value
of the variable @j is less than or equal to the value of @max_count.

set
@j =1
select
@max_count = max(row_id) from #alert_message

while(@j<=@max_count)

begin
select
@mail_subject = general_name+space(1)+’Replication Has Failed’ from
#alert_message where row_id = @j
select
@message = alert_message from #alert_message where row_id = @j

select
@mail_body =

Hi
Team,<br><br>
The
reason for failure is:<br><br>

‘+convert(varchar(100),@message)+’<br><br>
Regards,<br>

BCP
Administrator

Continues…

Pages: 1 2




Array

4 Responses to “SQL Server Email Alerts Notifications”

  1. Hi Satnam singh,

    grt work Awesome article
    I followed your article but i have one question . how can stored procedure usp_send_replication_alerts can send email automatically whenever replication fails ?

    Right now i can send email when i execute your stored procedure manually like

    EXEC [msdb].[dbo].[usp_send_replication_alerts]

    Any help is appreciated

    Thanks
    Niranjan

  2. I would like run this in my environment any step show me error, please help that I need this!!

  3. Hello Satnam Singh…
    I have also the same question as Niranjan.
    How can stored procedure(usp_send_replication_alerts) will send email automatically whenever replication fails??

    right now I have created trigger on sysreplicationalerts and it worked fine for sending mail automatically for define jobs in sql server agent. BUt when I deleted all jobs it didn’t work.

    create trigger send_mail
    on sysreplicationalerts
    after insert
    as
    begin
    EXEC [msdb]..[sp_send_dbmail]
    @profile_name = ‘testProfile’,
    @recipients = ‘it.ravi832@gmail.com; ravishankar_405@yahoo.com‘,
    @subject = ‘Error Message’,
    @body = ‘HI ALL !!! HOW ARE YOU’,
    @body_format = ‘HTML’
    end

    And I couldn’t fetch error message.

    I have same requirements like your company but I am unable to implement it.

    Can you explain your steps.

    Thanks in advance..

  4. Yes Satnam this is very useful, but if any of you have found out how to make this automated to receive an failure message directly when on failure it would be more useful. Should we have any scheduled jobs to kick that msdb exec sp step?
    Eben

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |