Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2012-09-17 : 19:32:57
|
| ( 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:Configuration: SQL2k8 R2 P2P Transactional Replication Wanted to test replication latency alerts during a db maintenance job/process...like 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 steps1. 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 Inserts3. 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 processReplication 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 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2012-09-19 : 11:31:08
|
| 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.Cheers - willI 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. |
 |
|
|
|
|
|
|
|
Latency alerts continue after all data replicated?