How to view the execution progress of distirbutionagent in SQLServer

During the transactionalreplicationtroubleshooting process, the following scenario is often encountered: the customer executes a few millions of lines of updates on the release end, resulting in performance degradation. The customer wants to know the progress of the current distributionagent, the percentage of completion, and decide whether to wait or skip this process. If you have completed 90%

During the transactional replication troubleshooting process, the following scenario is often encountered: the customer executes a few millions of lines of updates on the release end, resulting in performance degradation. The customer wants to know the progress of the current distribution agent, the percentage of completion, and decide whether to wait or skip this process. If you have completed 90%

During the transactional replication troubleshooting process, the following scenarios are often encountered:

The customer executes a few million lines of updates on the release end, resulting in performance degradation. The customer wants to know the progress of the current distribution agent, the percentage of completion, and decide whether to wait or skip this process. If 90% has been completed, it would be a pity to stop it rashly, And the rollback operation takes a long time.

The following describes how to view the progress.

If the distribution agent has enabled verbose log, you can view the progress through verbose log. Command id indicates the number of executed transactions; transaction seqno indicates the xact_seqno of ongoing transactions. Then run select count (*) From distribution .. msrepl_commands with (nolock) where xact_seqno = @ xact_seqno in distribution.

The comparison result shows the progress.

If verbose log is not enabled, it is troublesome. The specific steps are as follows.

1. Find the corresponding distribution agent name and publisher_database_id

Select * From distribution... msdistribution_agents

2. You can find the process id of the distribution agent by name. Execute the following statement on the distributor.

Select hostprocess from sys. sysprocesses where program_name = @ mergeAgentName

3. the process id of the same distribution agent process is the same, so you can use this process id (corresponding to the client process id in the trace ), use SQL server trace to obtain the statements that the distribution agent is executing on the subscriber side.

4. Suppose we get the following statement exec [dbo]. [sp_MSupd_dbota] default, random, 4, 0x02

5. Based on this stored procedure, we can get the corresponding aritlce_id.

Run sp_helptext in sub‑database to obtain the table name.

Obtain article_id. select article_id from msarticles where destination_object = @ from the distribution database query @Tablename

6. Run the following statement on subscriber to obtain the xact_seqno value of the subscriber database. (Please bring the distribution name obtained in step 1 to @ distribution_agent)

Select transaction_timestamp, * From MSreplication_subscriptions where distribution_agent = @Distribution_agent

7. You can find the xact_seqno currently being executed by the distribution agent. Add the publisher_database_id obtained in step 1, The article_id obtained in step 5th, and the xact_seqno obtained in the previous step to the following query.

Select xact_seqno, count (*) as number From distribution... msrepl_commands with (nolock) where publisher_database_id =@ Publisher_database_idAnd article_id =@ Article_id andXact_seqno> @ xact _SeqnoGroup by xact_seqno order by xact_seqno

8. The transaction is in the descending order and the number is large. You may ask why it is not the next xact_seqno obtained in Step 6 (select min (xact_seqno) From distribution .. msrepl_commands with (nolock) where publisher_database_id = @ publisher_database_id and xact_seqno> @ xact_seqno ).

9. Because distribution does not commit every transaction separately, it is committed based on CommitBatchSize and CommitBatchThreshold, which improves performance.

10. Execute sp_browsereplcmds @ xact_seqno and @ xact_seqno in the distribution data.

11. Use the statement obtained in Step 4 to find the current execution position.