hmmm thats in interesting one. Are you using Push or Pull replication? What version of SQL Server is this? Are the Subscriptions going to different servers? Is the Distributor Remote or Local on the Publisher?
Push, SQL 2008 R2, all on one server.
This next question is important. Are the missing transactions consistently for one article or a group of articles?
No its completely random, different articles in different publications, even where the publication has multiple articles only one is generally an issue at any given time.
i.e is it always consistently Article X that seems to be missing the updates or sometimes do X and Y replicate, but Article Z seems to have missed?
See last answer
Also how prevalent this across all your Publications and Articles? What percentage of articles are affected?
Under 1% at any given time, i.e. yesterday it was 1 article in 1 publication out of all the articles published.
From your question I have also assumed that if an Article fails to replicate the transaction, then this is affects all Subscribers? i.e if Article X is configured with three Subscribers, there's no chance that two of them will get the update and one will not?
All subscribers to the same article are affected.
Ok, so if you have answered that the Article(s) always missing the Transaction is consistently the same ones, then I would says script out your Publication containing X,Y,Z Articles and examine all the options specified on sp_addarticle and sp_addsubscription for those Articles to see if they differ.
Been there and done that, all our publications have been created from the same scripted template, all that changes anytime one is created is the source and destinations
If they dont differ then next analyse the sp_MSins, sp_MSdel, sp_MSIns stored procs for each related Article to see if they are strangely formatted somehow.
Trying to avoid that given there is 438 published articles, and the fact that its not consistently the same ones that fail, to give context it can be months inbetween this issue cropping up, and we never know which publication\article will be affected, the fact nothing gets logged as an issue in replication monitor or the system tables in the distribution database is what really concerns me the most
You could turn on detailed Logging for your distribution Agent to see if anything is thrown up in there when an update is not replicated.
I'll look into this
You could also check your Distribution and Log Reader Agent profiles to see if they are non default and if so what are the customized settings? I say this because You can set them to ignore consistency errors for example.
Again all are created from a template, and we have this exact same setup in another data centre without issue, but the ignore consistency errors option is a very good shout.
Also at your Subscribers does anyone have write access to the DBs (nobody should have)? could it be that your replicated change is simple being updated by some rogue Application process?
Definetly not, the only accounts that have write access are DBA and replication accounts, this syetm is very locked down to meet government security standards, all application access is via stored procedures and I have labourously been through all of them to make sure none of them do anything but read data.
If your SQL Server version is a bit out of date go through all the KB article for Cumulative Updates released after your version to see if there are any Replication fixes in them.
SQL Server 2008 SP2 CU4 from memory
Its a little behind current but when looking at the CU's issued since none of them have documented changes for replication.