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,
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_id2. 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] DESC3. 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'; ENDThis is the details I wanted to present here to catch up replication latency and hope you may like it.
Comments
Post a Comment