Monitoring and Troubleshooting Transactional Replication

• By Baya Dewald
• Apr 23, 2004

Like this article? We recommend
Microsoft SQL Server™ 2000: A Guide to Enhancements and New Features

In the second article in this series, Setting Up Transactional Replication, I showed you how to set up a simple publication and subscribe to it using replication wizards or scripts. After you set up replication, you need to ensure that it functions as expected. Furthermore, you need to see whether transactions get delivered to the subscribers in a timely fashion. Replication usually works very well, but as with any system, errors do crop up occasionally.

This article shows you how to check replication functionality and how to troubleshoot errors. As you might imagine, it is impossible to predict every error that you might encounter. Instead, I will show you how to detect errors and where to look for clues to troubleshoot replication issues.

Monitoring through the Replication Console

After you configure replication, the SQL Server Enterprise Manager displays a new folder called Replication Monitor at the distributor node. In fact, if you want to monitor replication but don't want to see other folders on the distribution server, you can right-click the Microsoft SQL Servers node under the console root and choose Show Replication Monitor Group. You should see a screen similar to that shown in the following figure.

Figure 1

Note that the Replication Monitor contains folders for numerous agents, even if you're not using some of them. On my server, I use only Snapshot, Log Reader, and Distribution Agents because they are involved in transactional replication. Clicking each folder displays the details of work performed by each type of agent. An alternative way to monitor replication agents is to expand the publishers folder and select the specific publication you want to check out. If I click D10ZF411, for example, I can examine details of the sample publication I created for the previous article. If you look in the right pane of Enterprise Manager while a particular publication is selected, you see some useful information, as shown in the following figure.

Figure 2

The first column in this view shows the type of agent being monitored. The Snapshot button is marked in red because it is stopped. Log Reader and Distribution agents have green arrows, indicating that both of them are running.

The Duration column tells us that it took 8 seconds to generate the initial snapshot. Furthermore this column informs us that Log Reader and Distribution agents have been running continuously for 7 hours and 38 minutes. The Delivery Rate column tells us that the Distribution agent has been delivering 21 commands per second on average. The Latency column is particularly interesting; replication latency measures the time it takes to deliver commands from the publisher to the distribution database and then to the subscriber(s). In this example, latency has been 7 seconds (7000 milliseconds) for the Log Reader agent and almost 10 seconds (9858 milliseconds) for the Distribution agent. The last three columns show the number of transactions, number of commands, and average number of commands per delivered transaction.

You can refresh the Replication Monitor view manually by right-clicking the publication and choosing Refresh or by letting the Enterprise Manager refresh it automatically. To change the automatic Refresh frequency, right-click the publication and choose Refresh Rate and Settings. Doing so activates the Refresh Rate and Settings dialog box shown in the following figure.

Figure 3