Create Alert Messages When SQL Server Transactional Replication Is Blocked

Recently we had an issue in our Production environment, on the Reporting Database Server the Transactional Replication procedure was being blocked due to execution of some heavy T-SQL queries and as a result business users were unable to view the latest data.

During our investigation, I had a looked at the Synchronisation status of the subscriber and I received the below error message:

The process is running and is waiting for a response from
the server.

On further investigation I noticed that due to the execution of a resource-intensive T-SQL query, the database was blocking.

Service Centre Operation Manager (the monitoring utility we use to monitor the servers) was not capturing an alert message. Based upon this, I decided to write a T-SQL script which will execute every 30 minutes and check if the Transactional Replication is functioning properly and alert a particular user or a team.

During the investigation of the case, I have noticed that whenever the system shows the error message as :

The process is running
and is waiting for a response from the server

it keeps on executing and at a later stage it throws a message as Query Timeout Expired which is stored in sysreplicationalerts table in the msdb database on the publisher server.

I queried this table to find the list of all the records ehere the Replication Process was halted using the below T-SQL:

select row_number() over(order by alert_id),[time],subscriber,
alert_error_text
from msdb..sysreplicationalerts
where
datepart(yyyy,[time]) = datepart(yyyy,getdate())
and datepart(mm,[time]) = datepart(mm,getdate())
and datepart(dd,[time]) = datepart(dd,getdate())
and
alert_error_text like '%query timeout%'

I first created 2 temporary tables named #replicationalerts and #subscriber_alert_message which will be used for conditional processing.

The schema for #replicationalerts temporary table is as follows:

create table #replicationalerts
(
replication_alert_id int,
[time] datetime,
subscriber varchar(100),
alert_error_message varchar(1000)
)

The schema for #subscriber_alert_message temporary table is as follows:

create table #subscriber_alert_message
(
subscriber_name varchar(1000),
 [time] datetime,
 alert_message varchar(1000)
 )

The data in a replication procedure which has been halted is transferred into the #replicationalerts temporary table using the below T-SQL.

insert #replicationalerts
select row_number() over(order by alert_id),[time],subscriber,
alert_error_text
from msdb..sysreplicationalerts
where
datepart(yyyy,[time]) = datepart(yyyy,getdate())
and datepart(mm,[time]) = datepart(mm,getdate())
and datepart(dd,[time]) = datepart(dd,getdate())
and alert_error_text like '%query timeout%'

Once this is done then I declared two local variables which will later be used in the conditional processing:

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

Whilst the value of the variable @i is less than or equal to the value of the variable @max_count, the fetching of records continues:

while(@i<=@max_count)
begin
select
@subscribername=subscriber from #replicationalerts where replication_alert_id = @i
select
@patindexname=patindex('%\%',@subscribername)
select
@count = @patindexname-1
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  'Subscriber Server Name', ,@time,@left_alert_error_message
set @i = @i+1
end

I then created a temporary table named #alert_message to hold the text of the alert message to be displayed to the users.

create table #alert_message
(
row_id int,
subscriber_name varchar(1000),
alert_message varchar(1000),
[time] datetime
)

The data in the temporary table was inserted using the #subscriber_alert_message, keeping in mind that only last 30 minutes data needs to be retrieved for which we have used the datediff function as shown below:

insert #alert_message
select
rank() over(order by subscriber_name,alert_message),
subscriber_name,
alert_message,
[time]
from
#subscriber_alert_message
where
datediff(mi,[time],getdate())<=30

I declared two local variables named @j and @max_count as shown below:

Declare @j int
Declare @max_count int
set @j =1
select @max_count = max(row_id) from #alert_message
while(@j<=@max_count)
begin
select @mail_subject = ‘Subscriber Server 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 Transactional Replication between the OLTP and Reporting Server is not happening properly.<br><br>
The reason for failure is:'+convert(varchar(100),@message)+'.'+'<br><br>
The time when the failure occured is:'+convert(varchar(100),@time)+'.'+'<br><br>
Regards,<br><br>
DBA Support Team
'
exec msdb..sp_send_dbmail @profile_name = 'DB Mail'
,@recipients ='semi comma seperated email address of the recipients'
,@subject = @mail_subject
,@body = @mail_body
,@body_format = 'HTML'
,@importance = 'High'
set @j = @j+1
end
drop table #alert_message
drop table #replicationalerts
drop table #subscriber_alert_message
end

This is just one such approach which I used on my production environment to alert the users in case if the transactional replication gets halted. If there are any suggestions or approaches then please let me know.




Related Articles :

One Response to “Create Alert Messages When SQL Server Transactional Replication Is Blocked”

  1. Replication already comes with inbuilt alerts that will alert you if any query time out occurs.

    Is this something different?

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 |