I have setup transactional replication in SQL Server and I am using PULL to retrieve the data from the subscriber.
Recently, I noticed that the data in my subscriber is not synced up with the database on the publisher server.
Also, I found the following issues:
- The replication monitor on the publisher indicates that the performance is critical and the latency is 00:04:25
- The replication monitor also indicates there are 56000 undistributed commands. (This has not changed, seems to be stuck)
- The "Distributor to Subscriber History" indicates there are 4 running sessions of the distribution agent.
I've setup verbose logging on the distribution agent job, and I am seeing this error message:
Raised events that occur when an agent's reader thread waits longer
than the agent's -messageinterval time. (By default, the time is 60
seconds.) If you notice State 2 events that are recorded for an agent,
this indicates that the agent is taking a long time to write changes
to the destination.
I have tried restarting the distribution agent job, but this doesn't seem to fix the issue. I am not really sure where to troubleshoot next.
Also, it is worth mentioning that the subscriber is running on a secondary SQL Server instance, on the same physical server as the publisher, so I would think latency should be low.
Best Answer
check it out the scripts on this question:
Replication Monitor Information using T-SQL
Just make sure you run them in the right place - publisher, distributor or subscriber.
first thing is to find out what is there to be replicated that has not been, and why. this will give you a starting point:
from Querying msdistribution_status slow
--this is to be run on your distributor server if you have one,
distribution
databaseand this one:
-- this is to be run on your publication server
this will find out what is pending, between the data you get here, and those queries above, it will give you some info
there should be only one process reading the log and marking the transactions as replicated otherwise you get errors like this one:
you mention:
that has an impact on the transaction log. I understand that you are talking about availability groups? check these scripts to see if it is healthy.
what is the size of your transactions?
what is the size and VLFs of your transaction logs?
how it works?
The reader thread of the Log Reader executes
sp_replcmds
to pull commands from the transaction log. The stored procedures below can be executed in SQL Server Management Studio to track time required to pull next pending replicated transaction.How long do these command take to execute? How many commands were returned?
Scanning a large transaction log for a single transaction containing a million+ row modifications may take 20+ minutes to complete.
The execution time for this query approximates the startup time for the Log Reader to replicate this same transaction. Display 1st pending transaction metadata such as publication_id, article id, xactid, and command_type.
Is this command part of normal database operation or an unexpected large batch of commands?
this will give you a good starting point, however, it is a complex environment, and there are things not so clear from your question.
I tried to answer the best I could, if you let me know how it goes, I can improve my answer.