Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How to troubleshoot merge replication performance issues by using trace flag 101


View products that this article applies to.

Introduction

Typically, the Replication Merge Agent takes a long time to replicate changes. To determine which step of the merge replication synchronization process takes the most time, use trace flag 101 together with merge agent logging. To do this, use the following parameters for the merge agent parameters, and then restart the agent:
  • -T 101
  • -output
  • -outputverboselevel


Note If you have to write stats to the <Distribution server>..msmerge_history table, use trace flag -T 102.

↑ Back to the top


More Information

A sample output of the merge agent after merge replication synchronization finishes is as follows:

************************************************************** CONNECTION TIMES --> time took to establish the connection to the servers. Publisher (all connections) 156 msec   Subscriber (all connections) 32 msec Distributor 93 msec ************************************************************** UPLOAD COUNTERS  --> upload phase (changes from the Sub to the Pub) stats MakeGeneration Time = 343 msec. InsertGenHistory Time = 31 msec. UpdateGenHistory Time = 0 msec. ProxiedMetadata Time = 0 msec. ************************************************************** DOWNLOAD COUNTERS  --> download phase (changes from the Pub to the Sub) stats MakeGeneration Time = 219 msec. InsertGenHistory Time = 0 msec. UpdateGenHistory Time = 0 msec. ************************************************************** RETENTION-BASED CLEANUP STATISTICS --> sp_mergemetadataretentioncleanup proc stats Publisher: Cleanup Time 281 msec MSmerge_genhistory rows cleaned up 0 MSmerge_contents rows cleaned up 0 MSmerge_tombstone rows cleaned up 0 Subscriber: Cleanup Time 187 msec MSmerge_genhistory rows cleaned up 0 MSmerge_contents rows cleaned up 0 MSmerge_rowtrack rows cleaned up 0 MSmerge_tombstone rows cleaned up 0 ************************************************************** RETRY STATISTICS Retry Time (Upload) 0 msec. Retry Time (Download) 0 msec. Total changes retried 0 Number of Iterations through rows needing retry 0 Total number of changes that failed despite retry 0 ************************************************************** PROXY METADATA QUEUE COUNTERS Queue Full: Number of Waits: 0, Total Wait Time: 0 msec ************************************************************** Distributor-side History Logging Time = 219 msec. Number of Distributor-side History Messages Logged = 11 Subscriber-side History Logging Time = 295 msec. Number of Subscriber-side History Messages Logged = 11 ************************************************************** 2013-05-28 17:24:11.820 OLE DB Subscriber '< SQL Server name >\sql2008r2': DBCC SQLPERF (NETSTATS)  2013-05-28 17:24:11.822 OLE DB Publisher '< SQL Server name >\SQL2008R2':  DBCC SQLPERF (NETSTATS)  2013-05-28 17:24:11.824 OLE DB Distributor '< SQL Server name >\SQL2008R2':  DBCC SQLPERF (NETSTATS)  NETWORK STATISTICS Server  Reads  Writes  Bytes Read Bytes Written Publisher 74  74  19112  37526 Subscriber 73  73  19032  36931 Distributor 75  75  19192  38121 ************************************************************** NETWORK STATUS Network Connection: The computer has one or more LAN cards that are active. Network link speed: Destination Incoming  Outgoing Publisher Unreachable  Unreachable Subscriber Unreachable  Unreachable Distributor Unreachable  Unreachable ************************************************************** 

↑ Back to the top


References

312292 How to enable replication agents for logging to output files in SQL Server

↑ Back to the top


Keywords: kb, kbsurveynew, kbtshoot, kbexpertiseadvanced

↑ Back to the top

Article Info
Article ID : 2892633
Revision : 1
Created on : 1/7/2017
Published on : 10/14/2013
Exists online : False
Views : 1145