Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Replication (2008)
 Latency alerts continue after all data replicated?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

332 Posts

Posted - 09/17/2012 :  19:32:57  Show Profile  Reply with Quote
( i posted this in the msdn forums...but no answer yet. hopefully, you folks don't mind me posting here as well for help!)

Hi All -

Here is a quick summary of the issue and question:

- We have set up replication latency alerts if latency exceeds 30 mins.
- We have been able to sucessfully test out simulated latency issues without any problems.
- What we wanted to do is test out the behavior of the latency alerts if there was db maintenance type of job running concurrently with replication.
- Set up a index rebuild job (details below), which we started and then ran a proc to insert some data.
- We continue getting replication latency alerts EVEN after all the data has finally been replicated. The alerts don't stop until we run the Insert Tracer Token process, or add some new data.
- Why would this alerting continue to happen? Is there anything we can do to prevent the "false" latency alerts?

The Details:

SQL2k8 R2
P2P Transactional Replication

Wanted to test replication latency alerts during a db maintenance job/ an index rebuild.

Have a job that has the following steps
- Turns off the latency alert (set ShouldAlert = 0)
- Backup Transaction Log
- Rebuild Indexes
- Backup Transaction Log
- Turn ON latency alert (set ShouldAlert = 1)

Testing steps
1. Started Index Rebuild job (see above)
2. Executed stored proc that would insert data into 4 tables, with a WAITFOR DELAY between the sets of inserts of 1 minute. I ran a total of three sets of Inserts
3. Waited and watched...

Results (the following are the times of various events)
14:26 - Started index rebuild job
14:26 to 14:28 - Data inserted into tables on Publisher node
14:26 - one "set" of data inserted into tables on Subscriber node
15:51 - 2nd/3rd sets of data inserted into tables on Subscriber node

15:32 - I received my first replication latency warning/alert.
The warnings/alerts continued every 5 minutes until 17:02 when I ran the Insert Tracer Token process

Replication Monitor Log/Details vs SQL Server Log/Details
Repl Monitor
"Log reader Agent is scanning the transaction log for commands to be replicated.
Approximately NNNNN log records have been scanned in pass # N, 1 of which were
marked for replication, elapsed time NNNN (ms)"

First entry is at 14:31 - this is pass #1
Pass #2 starts at 14:56
Pass #3 starts at 15:24

Scanning log entries end at 15:51
Log indicates "No replicated transactions are available"

SQL Server Log
Starts showing "Threshold Latency" warnings at 15:51...which is when the 3rd pass ends and when i started receiving the email alerts.

However, as metioned above...that is when the last two sets of data were finally replicated to the subscriber node.

And i can validate this because i have an Insert Trigger on one of the tables that updates a datetime column with the current datetime (after insert).

The threshold latency warnings also stop at 17:02

So, as we can see, the Replication log shows more latency alerts...when the log reader agent is scanning the transaction log. This make sense. But once the log scanning is complete and the data is finally "replicated" to the subscriber...we start getting the alerts and they continue until the tracer token is inserted.

Thanks for any thoughts or help

- will

Constraint Violating Yak Guru

332 Posts

Posted - 09/19/2012 :  11:31:08  Show Profile  Reply with Quote
Hi All -
We still don't have any answers, but someone on the MSDN forums replied to me and provided some great info, so I thought I'd post it here in case it helps anyone else.

- will

I thought initially that updating the alert in msdb..sysreplicationalerts and setting status = 1 would alleviate the alert from reoccurring but this is not the case.

What I do know is the Transactional replication latency alerts will continue to occur until the value of cur_latency for a subscription in MSreplication_monitordata or current_delivery_latency in MSdistribution_history drops below the specify threshold configured, so there is a direct correlation there. Also, cur_latency and current_delivery_latency get updated when tracer tokens are inserted and if the latency has subsided, then the cur_latency and current_delivery_latency will drop, thus eventually alleviating the alert from reoccurring.

So one idea would be to create a response job for Transactional replication latency alert to repeatedly post tracer tokens until cur_latency and current_delivery_latency drops below the threshold configured.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000