Sql-server – Transactional Replication not sending all commands

replicationsql serversql-server-2008-r2transactional-replication

I have a transactional replication topology as follows:

NY Publication Database same server holds distributor

NY – Subscriber
LON – Subscriber
HK – Subscriber

We are using bog standard, one-way transactional replication. All machines SQL 2008 R2

The snapshot gets applied fine to all subscribers, and then starts distributing commands, as you would expect, but to the one subscriber only (London), a whole bunch 2000-4 million commands just get "stuck". You can see the commands in msrepl_commands, you can see the transactions in msrepl_transaction. Replication monitor shows that there are xxxxx undelivered commands. All commands are being delivered without issue to the other 2 subscribers.

The problem is that they just stay there. Replication monitor says that there is nothing to be replicated, and nothing happens, they just stay where they are. The only way I have found around it, it to tear down the subscription and re-initialize!?!

I am aware that there are network "blips" between NY and Lon, and I can see in msrepl_errors, that there are some communication failures. However, I would expect replication to pick them back up? The distribution agent is running fine.

How do I get around this issue, and force SQL Server to pick these commands up, and deliver them.

Best Answer

Examine the output of sp_helpsubscriptionerrors. One issue I have seen is with DDL commands; they are treated differently in replication so if manual schema changes are being made to the Publisher, they can sometimes fail. There may also be specific DML commands failing on the subscriber due to transactional inconsistencies. If this is the case, you can use the distribution agent profile called "Continue On Data Consistency Errors" which will ignore duplicate key and other errors.

Latency could be causing the problem (according to MS, latency and throughput are the most important factors in transactional replication); you can track that with sp_posttracertoken. Both the Log Reader and Distribution agents have parameters which can be tweaked to compensate for that. Try lowering these values, particularly on the Log Reader.

Distribution Agent (from MSDN):

MaxDeliveredTransactions Is the maximum number of push or pull transactions applied to Subscribers in one synchronization. A value of 0 indicates that the maximum is an infinite number of transactions. Other values can be used by Subscribers to shorten the duration of a synchronization being pulled from a Publisher.

CommitBatchThreshold Is the number of replication commands to be issued to the Subscriber before a COMMIT statement is issued. The default is 1000.

Log Reader Agent (from MSDN):

MaxCmdsInTran Specifies the maximum number of statements grouped into a transaction as the Log Reader writes commands to the distribution database. Using this parameter allows the Log Reader Agent and Distribution Agent to divide large transactions (consisting of many commands) at the Publisher into several smaller transactions when applied at the Subscriber. Specifying this parameter can reduce contention at the Distributor and reduce latency between the Publisher and Subscriber. Because the original transaction is applied in smaller units, the Subscriber can access rows of a large logical Publisher transaction prior to the end of the original transaction, breaking strict transactional atomicity. The default is 0, which preserves the transaction boundaries of the Publisher.

Related Solutions

Sql-server – Replication between SQL Server 2005 – 2005

I have found a solution to my problem. Beware though, the steps outlined below broke regular replication (or so it seems). Nevertheless, I was able to get replication running, and now there are no undelivered commands building up in the distribution database:

I Command You To Deliver Those Commands

The solution provided there is to run:

exec sp_changepublication
    @publication = 'xxxxx',
    @property = N'allow_anonymous', 
    @value = 'false'
exec sp_changepublication
    @publication = 'xxxxx',
    @property = N'immediate_sync',
    @value = 'false'

Sql-server – Transactional Replication issues

First, you can set up Canary table and other detailed monitoring of transactional replication per Kendra Little'ss article Monitoring SQL Server Transactional Replication. Canary tables, the easiest step in the article, are described as:

It’s easy to build your own system for tracking replication latency for each publication. Here are the ingredients for the simplest version:

Add a table named dbo.Canary_PubName to each publication

dbo.Canary_PubName has a single row with a datetime column in it

A SQL Server Agent job on the publisher updates the datetime to the current timestamp every minute

A SQL Server Agent job on the subscriber checks dbo.Canary_PubName every minute and alerts if the difference between the current time and the timestamp is greater than N minutes

Then take a look at the activity happening on the tables being replicated. It sounds like most of your problems are related to very high activity - your log size moving from 3GB with only 13-20% used, to 3.5GB (meaning all 3GB was used) is an indicator of big transactions, which may be saturating your replication bandwidth.

Also, if they take 20-30 minutes to replicate when reinitialized, that's a combination of their size and your free bandwidth.

So, the general fixes are:

Increase the bandwidth
Reduce the amount of data
- Use the smallest integer based keys you can, and simply replicate lookup tables with descriptions, rather than have descriptions in your tables.
  - BIT when possible, then TINYINT, then SMALLINT, then INT, then BIGINT where you're forced to.
- Replicate indexed views instead of actual tables (publisher = indexed view, subscriber = table) - send over only the required columns, and only the required rows.
- Don't use NCHAR/NVARCHAR unless you need to - use CHAR/VARCHAR when you know you only have, or perhaps even should have, no UCS-2 "Unicode" data.
  - Use CHAR when the average size of data is within 2 bytes of the maximum field size, since VARCHAR has a 2 byte overhead to store length
Reduce the number of transactions on the replicated data
- Whatever you've got happening that causes all that activity on the replicated tables, analyze it. Is it required? Could it be re-written to minimize the amount of data being replicated?
  - Don't touch the same row more than once, i.e. instead of INSERT, UPDATE, UPDATE, UPDATE, just a single INSERT to the final table - perhaps work in #temp tables before then? And so on and so forth.
- If they don't absolutely need up to the minute data on any given table, then don't replicate your the core tables that get all this activity.
  - For each non-realtime-required table, create reporting tables on the publisher that get updated only every so often (once a week, once a day, once an hour, whatever), and replicate those.
  - And make sure to carefully use only the UPDATEs, INSERTs, and DELETEs that are required - the goal is to minimize replication load.
  - Don't use MERGE; it has too many issues.
Split the tables up into multiple publications, so you aren't taking a snapshot of, and reinitializing, every table every time you have to add or remove one.
- I.e. If you add/drop/alter an article in Publication C, you need to reinitialize and snapshot that publication, but publications A, B, D, E, and F aren't affected by this.
  - And thus it moves less data, which in turn takes less time.
  - And the subscriber retains access to all the articles in all the other publications
  - So split them up along functional usage lines; if tables A, E, and G are used together most of the time, while C and D aren't involved in most of those queries, that's a natural split that will leave your client happier; at least some functions continue unobstructed during re-initialization of either set.
Increase the bandwidth