I’ve posted this flow before on my BLOG but I wanted to include the stored procedure calls used by Transactional Replication.
While troubleshooting Replication performance/latency we’ll try and identify which stored procedure executing from which Replication Agent has the highest IO or highest CPU usage. These usually are a great place to start the investigation. To analyze the processes flow we use SQL Profiler and/or SQLDIAG and capture RPC:COMPLETED Events. We also add –OUTPUT tracking parameter to the Agent which writes a text file of each agent execution step along with a time stamp.
To learn more about troubleshooting LogRead.exe and Distrib.exe check out: http://blogs.msdn.com/chrissk/archive/2009/05/25/transactional-replication-conversations.aspx
Chris SkorlinskiMicrosoft SQL Server Escalation Services