Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Troubleshooting Replication Errors
So far, you’ve seen a quick status of publications and common replication jobs, but what if any of your publications is having issues? How can you review the summary of the snapshot, log reader, and distribution agents’ activities? Let’s examine the Subscription Watch List tab, which is available when you connect to any publisher. If you right-click a subscription on this tab, you’ll see the options you were looking for.
)
Right-click a distribution agent to view details of the agent’s activity, start or stop synchronizing the subscription, reinitialize it, refresh it, view distribution agent’s profile details, or see distribution job properties.
Unlike previous releases of the software, SQL Server 2005 provides a separate screen with multiple tabs for viewing subscription details within Replication Monitor, as shown next.
)
Note that this screen has three tabs; the first one shows the details of log reader agent’s activity titled Publisher to Distributor History. The second tab provides details of the distribution agent’s activities, as shown here.
)
At first, you might feel awkward having two separate tabs for reviewing log reader and distribution agents’ status because quick status of both agents was available from a single screen in previous versions. However, even previous releases of SQL Server required opening a different screen to review each agent’s history; the difference is that SQL Server 2000 would require you to close the window showing distribution agent’s history before you could review the log reader agent’s activities. Now we can review the history of both agents through the same dialog box, but from two different tabs.
Both tabs have the option of limiting the number of synchronizations; the available options include the following:
- All synchronizations
- Synchronizations with errors
- Synchronizations in the last 24 hours
- Synchronizations in the last 2 days
- Synchronizations in the last 7 days
- The last 100 synchronizations
Furthermore the Action menu enables you to start/stop synchronizing, review subscription properties, reinitialize the subscription, configure the screen to refresh automatically or manually, and examine each replication agent’s properties.
The third tab on this screen presents one of the best improvements of monitoring functionality—it shows you the total number of commands in the distribution database waiting to be applied to the subscribers and estimated time for delivering these commands.
)
Previous versions of SQL Server didn’t have such functionality in replication monitoring tools. You could run a query against system tables in distribution databases to see how many commands were waiting to be delivered, but there was no way to accurately estimate how long it would take to deliver them.
If you want to examine snapshot or log reader agent’s activity history, click a single publication within the left pane of Replication Monitor and then navigate to Warnings and Agents tab. The bottom pane of this tab will let you pick a snapshot agent or log reader agent and examine its history by right-clicking the agent and choosing View Details, as shown next.
)
This user interface resembles screens we used to use to administer replication with previous versions of SQL Server. Within SSMS there is another handy way to check the status of each replication agent and start it, if desired. If you right-click the publication under the local publications folder you’ll see these options: View Snapshot Agent Status and View Log Reader Agent Status. If you right-click any subscription, you’ll have an option to View Synchronization Status that shows you the status of the distribution agent. For example, the following screenshot shows the status of the log reader agent.
)
Clicking the Monitor button on this screen launches the Replication Monitor. Screens for checking the status of snapshot and distribution agent are identical to this one.
Now let’s intentionally break replication to see how Replication Monitor can help us troubleshoot errors. I will delete a row in the subscription database first and then delete the same row on the publisher.
DELETE dimCurrency WHERE CurrencyKey = 1
The DELETE statement executed on the publisher will be replicated, but it won’t find the row it’s trying to delete on the subscriber. Replication Monitor promptly reports the error, as shown following.
)
So far, so good. But how can we fix the problem? The tool says that the row was not found at the subscriber, but it doesn’t tell us what command caused the error. Regretfully, Replication Monitor did a better job with this in SQL Server 2000. Now we have to take an extra step to find the offending command. Let’s copy the transaction sequence number reported by the Replication Monitor and use the system procedure sp_browsereplcmds (found in the distribution database) to track down the statement that instigated the replication failure:
EXEC sp_browsereplcmds ’0x0000017400000E54000700000000’
Results:
|
Xact_seqno |
originator_srvname |
Originator_db |
article_id |
type |
partial_command |
|
0x0000017400000E540007 |
NULL |
NULL |
10 |
30 |
0 |
|
hashkey |
originator_publication_id |
originator_db_version |
originator_lsn |
|
1 |
NULL |
NULL |
0x00000000000000000000 |
|
Command |
command_id |
|
{CALL [sp_MSdel_dboDimCurrency] (1)} |
1 |
Now we can tell that execution of sp_MSdel_dboDimCurrency with the parameter of 1 caused replication to break. We can easily fix this error with one of two methods:
- Insert a "dummy" record on the subscriber with the currency_id = 1. Because replication is about to delete this record, it doesn’t matter what data the record contains; we’re simply adding it to let replication take its course.
- Configure replication agent to skip the error caused by the missing row: error number 20598. We can skip the error by modifying the distribution agent’s profile, which can be accessed by right-clicking the distribution agent and choosing Agent Profile. Doing so will display the profiles created by default, as shown in the following figure.
)
If you click the ellipsis button next to any profile you’ll have the opportunity to review the settings of each profile. However, you must create a new profile to change the default settings. The screen for configuring new distribution agent’s settings is activated by clicking the New button (shown next).
)