- Monitoring through the Replication Console
- Troubleshooting Replication Using Enterprise Manager
- Replication Performance Counters
- What Happens If...
- Troubleshooting Errors
- Distribution Database Stored Procedures
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Troubleshooting Replication Using Enterprise Manager
The Replication Monitor is a powerful tool for checking the status and performance of replication agents. Furthermore, you can use this tool to troubleshoot replication problems as well. To demonstrate this, I will force the distribution job to fail.
As discussed in previous articles, subscribing databases are typically used for read-only purposes. Now suppose that user john doe decided to delete a record from the subscriber server with the following statement:
DELETE authors WHERE au_lname = 'smith'
This statement removes a single row in the authors table. Now suppose user jane doe updated a record on the publisher with the following statement:
UPDATE authors SET phone = '394 302-2019' WHERE au_lname = 'smith'
This statement works fine on the publisher, but when the Distribution agent attempts to move this transaction to the subscriber, the row that is to be updated does not exist. the Replication Monitor immediately displays a red cross on the Distribution agent. Right-clicking the Distribution agent and choosing Error Details brings up the following screen.
)
Figure
4This screen is quite informative: The error occurred while replicating a command from D10ZF411 to D10ZF411\subscription_srv; there was a problem executing stored procedure sp_MSupd_authors because the row this command tried to update does not exist in the subscriber database. What is sp_MSupd_authors? Recall that when we set up replication (again, refer to "Setting Up Transactional Replication"), we had an option to replace the INSERT, UPDATE, and DELETE commands with stored procedures. Sp_MSupd_authors is a procedure that is executed in the subscriber database every time the authors table is updated on the publisher.
If you click the View Agent button on the screen, you will see the details of the Distribution agent job.
)
Figure
5If you want to receive E-mail or pager notifications each time the Distribution agent encounters an error, click the Notifications tab and configure the job to notify you.
To view further details about the Distribution agent, click the Agent History button on the Error Details screen. Agent History shows you the details of actions the Distribution agent has performed, including start time, time of last action, duration, delivery latency and more.
)
Figure
6 ***Insert pavliashvili_06.jpg***
Note that you can limit the output displayed in Agent History to the last 100 sessions (the default filter), sessions with errors, sessions in the last 2 days, sessions in the last week, or sessions in the last 24 hours. Although we see some detail of the error on the Agent History screen, we don't see the actions that the agent performed immediately before the error. To see details of the agent's actions, click the Session Details button, which activates the Latest History of Distribution Agent screen.
)
Figure
7The agent has been initialized, has applied the schema script (CREATE TABLE), created indexes on the authors table, bulk-copied data into the table, and then failed because the row wasn't found at the subscriber. In this simple scenario, the actions performed before the error are not related to the error itself. However, viewing session details can often help you find the clues about why replication agents aren't working.
Another term you need to learn for troubleshooting and tuning replication is agent's profile. Each replication agent operates using a specific profile, which is a set of parameters used while executing the agent. You can use the default profiles or create a new profile that has a different set of parameters. If you right-click Snapshot, Log Reader, or Distribution agent, you see the Agent Profiles menu option. If you choose this option and then click the View Details button, you see the following screen.
)
Figure
8 *
The following table explains each parameter used by the Log Reader agent:
|
Parameter |
Meaning |
Default Value |
|
History Verbose Level |
Amount of history recorded by the agent |
1 means that existing rows are overwritten |
|
Login Timeout |
Number of seconds before login to the publisher times out |
15 seconds |
|
Polling Interval |
Number of seconds the transaction log of the published database is queried |
10 seconds |
|
Query Timeout |
Number of seconds before queries executed by the agent time out |
300 seconds |
|
Read Batch Size |
Number of transaction log records read in a single batch |
500 seconds |
In a typical replicated system with a dedicated distribution server, the default agent profiles should work well. However, if you want to tweak parameter values used by each replication agent, you can create a new agent profile by clicking the New Profile button in the Agent Profiles screen.
)
Figure
9NOTE
Agent Profile settings are stored in the MSDB database in the MSagent_profiles and MSagent_parameters tables.