You are currently offline, waiting for your internet to reconnect

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

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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.
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 msecDistributor 93 msec**************************************************************UPLOAD COUNTERS  --> upload phase (changes from the Sub to the Pub) statsMakeGeneration 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) statsMakeGeneration Time = 219 msec.InsertGenHistory Time = 0 msec.UpdateGenHistory Time = 0 msec.**************************************************************RETENTION-BASED CLEANUP STATISTICS --> sp_mergemetadataretentioncleanup proc statsPublisher:Cleanup Time 281 msecMSmerge_genhistory rows cleaned up 0MSmerge_contents rows cleaned up 0MSmerge_tombstone rows cleaned up 0Subscriber:Cleanup Time 187 msecMSmerge_genhistory rows cleaned up 0MSmerge_contents rows cleaned up 0MSmerge_rowtrack rows cleaned up 0MSmerge_tombstone rows cleaned up 0**************************************************************RETRY STATISTICSRetry Time (Upload) 0 msec.Retry Time (Download) 0 msec.Total changes retried 0Number of Iterations through rows needing retry 0Total number of changes that failed despite retry 0**************************************************************PROXY METADATA QUEUE COUNTERSQueue Full:Number of Waits: 0, Total Wait Time: 0 msec**************************************************************Distributor-side History Logging Time = 219 msec.Number of Distributor-side History Messages Logged = 11Subscriber-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 STATISTICSServer  Reads  Writes  Bytes Read Bytes WrittenPublisher 74  74  19112  37526Subscriber 73  73  19032  36931Distributor 75  75  19192  38121**************************************************************NETWORK STATUSNetwork Connection:The computer has one or more LAN cards that are active.Network link speed:Destination Incoming  OutgoingPublisher Unreachable  UnreachableSubscriber Unreachable  UnreachableDistributor Unreachable  Unreachable**************************************************************
References
312292 How to enable replication agents for logging to output files in SQL Server
Properties

Article ID: 2892633 - Last Review: 10/14/2013 16:35:00 - Revision: 1.0

Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Web, SQL Server 2012 Enterprise Core, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Web, Microsoft SQL Server 2008 R2 Workgroup, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Web, Microsoft SQL Server 2008 Workgroup, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Evaluation Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbsurveynew kbtshoot kbexpertiseadvanced KB2892633
Feedback
if?DI=4050&did=1&t=">