Recently I had posted for one issue I faced for replication trace token
, Could not find the Distributor or the distribution database for the local server. Hope you read and liked it. Today I am writing for replication latency and
its alert and I am using one script to post a tracer token which I used same in
an earlier post where you can see the script there and details for same. So move on the topic here as how can we get replication latency information and alert for same. Putting in steps,
1. Post tracer tokens : Schedule a script to post a tracer tokens frequently, says 5
minutes, which posts a
trace token into the transaction log at the Publisher and begins the process of tracking latency statistics and run this script at publisher,
USE publisherdb
GO
DECLARE @out_tracer_token_id INT
EXEC sys.Sp_posttracertoken
@publication = N'<Publication Name>', -- Put Publication name here
@tracer_token_id=@out_tracer_token_id out
SELECT @out_tracer_token_id
2. Script : After scheduling above scripts every 5 minutes, which will put latency history in MStracer_history system table in distribution database, following script use to get details for the latency from publisher to distributor and distributor to subscriber which belongs to distribution database,
USE distribution
GO
SELECT publisher_db AS
[PublisherDB]
,
publication
AS [Publication],
name AS
[Subscriber],
subscriber_db AS
[SubscriberDB],
RIGHT('0' + Cast([timetosubscriber]/3600 AS VARCHAR(3)), 2)
+ ':'
+ RIGHT('0' + Cast(([timetosubscriber] % 3600) / 60 AS VARCHAR(2)), 2)
+ ':'
+ RIGHT('0' + Cast(([timetosubscriber] % 60) AS VARCHAR(2)), 2) AS
[DistToSubLatency],
RIGHT('0' + Cast([timetopublisher]/3600 AS VARCHAR(3)), 2)
+ ':'
+ RIGHT('0' + Cast(([timetopublisher] % 3600) / 60 AS VARCHAR(2)), 2)
+ ':'
+ RIGHT('0' + Cast(([timetopublisher] % 60) AS VARCHAR(2)), 2) AS
[PubToDistLatency],
RIGHT('0' + Cast([totaltime]/3600 AS VARCHAR(3)), 2)
+ ':'
+ RIGHT('0' + Cast(([totaltime] % 3600) / 60 AS VARCHAR(2)), 2)
+ ':'
+ RIGHT('0' + Cast(([totaltime] % 60) AS VARCHAR(2)), 2) AS
[TotalLatency]
FROM (SELECT DISTINCT msda.publisher_db,
syss.name,
msda.subscriber_db,
msda.publication,
publisher_commit,
distributor_commit,
Datediff(ss, publisher_commit, distributor_commit) AS
[TimeToPublisher],
subscriber_commit,
Datediff(ss, distributor_commit, subscriber_commit) AS
[TimeToSubscriber],
Datediff(ss, publisher_commit, distributor_commit)
+ Datediff(ss, distributor_commit, subscriber_commit) AS
[TotalTime]
FROM mstracer_history msth
INNER JOIN msdistribution_agents msda
ON msth.agent_id = msda.id
INNER JOIN sys.servers syss
ON msda.subscriber_id = syss.server_id
INNER JOIN mstracer_tokens
ON msth.parent_tracer_id = mstracer_tokens.tracer_id
WHERE subscriber_commit > Dateadd(mi, -5, Getdate())) res
ORDER BY [totaltime] DESC
3. Alert : As we
have seen a script to get the latency for replication
, herewith I am sharing a script to get
alerts for
highest latency, In script logic is implies to send an alert only when
a publisher to distributor or distributor to subscriber latency exceed 1 minute. You can schedule this script to every 5 minutes as we use condition to scan latency history inserted in
the last 5 minutes,
USE distribution
GO
DECLARE @subject SYSNAME
DECLARE @body VARCHAR(4000)
DECLARE @SubscriberTime VARCHAR(20)
DECLARE @PublisherTime VARCHAR(20)
DECLARE @TotalTime VARCHAR(20)
DECLARE @PublisherDB VARCHAR(20)
DECLARE @Publication VARCHAR(20)
DECLARE @Subscriber VARCHAR(20)
DECLARE @SubscriberDB VARCHAR(20)
SELECT @PublisherDB = publisher_db,
@Publication = publication,
@Subscriber = name,
@SubscriberDB = subscriber_db,
@SubscriberTime = RIGHT('0' + Cast([timetosubscriber]/3600 AS VARCHAR(3))
, 2)
+ ':'
+ RIGHT('0' + Cast(([timetosubscriber] % 3600) / 60 AS
VARCHAR
(2)), 2)
+ ':'
+ RIGHT('0' + Cast(([timetosubscriber] % 60) AS VARCHAR
(2)), 2
),
@PublisherTime = RIGHT('0' + Cast([timetopublisher]/3600 AS VARCHAR(3)),
2)
+ ':'
+ RIGHT('0' + Cast(([timetopublisher] % 3600) / 60 AS
VARCHAR(2
)), 2)
+ ':'
+ RIGHT('0' + Cast(([timetopublisher] % 60) AS VARCHAR(2
)), 2),
@TotalTime = RIGHT('0' + Cast([totaltime]/3600 AS VARCHAR(3)), 2)
+ ':'
+ RIGHT('0' + Cast(([totaltime] % 3600) / 60 AS VARCHAR(2)),
2)
+ ':'
+ RIGHT('0' + Cast(([totaltime] % 60) AS VARCHAR(2)), 2)
FROM (SELECT DISTINCT msda.publisher_db,
syss.name,
msda.subscriber_db,
msda.publication,
publisher_commit,
distributor_commit,
Datediff(ss, publisher_commit, distributor_commit) AS
[TimeToPublisher],
subscriber_commit,
Datediff(ss, distributor_commit, subscriber_commit) AS
[TimeToSubscriber],
Datediff(ss, publisher_commit, distributor_commit)
+ Datediff(ss, distributor_commit, subscriber_commit) AS
[TotalTime]
FROM mstracer_history msth
INNER JOIN msdistribution_agents msda
ON msth.agent_id = msda.id
INNER JOIN sys.servers syss
ON msda.subscriber_id = syss.server_id
INNER JOIN mstracer_tokens
ON msth.parent_tracer_id = mstracer_tokens.tracer_id
WHERE subscriber_commit > Dateadd(mi, -5, Getdate())) res
WHERE ( [timetopublisher] > 60
OR [timetosubscriber] > 60 )
-- Fetch if publisher to distributor or distributor to subscriber latency greater than 60 seconds
ORDER BY [totaltime] DESC
SET @subject='Replication Latency Alert'
SET @body=
'Replication latency exceeded the highest acceptable replication delay'
SET @body=@body
+ 'Publisher to Distributor: ' + @PublisherTime
+ 'Distributor to Subscriber: '+ @SubscriberTime
+ 'Total Delay: '+ @TotalTime
+ 'Publication: '+ @Publication
+ 'Subscriber: ' + @Subscriber
IF ( @body IS NOT NULL )
BEGIN
EXEC msdb.dbo.Sp_send_dbmail
@recipients = 'prajapatipareshm@gmail.com',
@subject = @subject,
@body = @body,
@profile_name = '<Profile name>',
@body_format = 'HTML';
END
This is the details I wanted to present here to catch up replication latency and hope you may like it.
