More DBA job interview questions and answers at
What is Replication Monitor and how it can be used to monitor replication activity and performance?
Replication Monitor is an interface provided by SQL Server 7.0 Enterprise Manager (EM), which allows you to monitor the activity of each replication agent individually AND the activity of all agents at the publication level.
An X mark will be displayed against the replication agents in case of any errors. Different actions of the replication agents are indicated using different icons.
Replication monitor refreshes the results pane and the server tree every 10 seconds by default. To get more real time information, change these settings, so that the contents of replication monitor are refreshed more often. To do this, right click on the Replication Monitor and select ‘Refresh Rate and Settings’. You can always do a manual refresh by right clicking on the nodes in the server tree and selecting ‘Refresh’. You can also press ‘F5’ key to refresh, when the focus is in the results pane.
By right clicking on the Replication Monitor and selecting ‘Select Columns’ you can customize the information that is being displayed about each agent.
You can change the schedules of the agent, add steps to the agent, and set notifications, by right clicking on the agent and selecting ‘Agent Properties’. You can change or edit the profiles of replication agents by right clicking on the agent and selecting ‘Agent History’. You can view the current error messages and history of the replication agent by right clicking on the agent and selecting ‘Error Details’ and ‘Agent History’ respectively. Also you can stop and start agents by right clicking on the agent.
Replication Monitor picks up the agent history from the replication history tables that are stored in the distribution database. You can go through the following tables to view the history:
Sometimes replication monitor shows X marks indicating that there is an error, though there are no errors. Don’t panic! Closing and opening the EM will clear these X marks, in the worst case we need to recycle the server.
We can log the complete activity of a replication agent to an output file. To capture the detailed activity to a file do the following:
- Right click on the agent and select ‘Properties’.
- In the properties dialog box, click on the ‘Steps’ tab.
- Double click on the ‘Run Agent’ step.
- In this tab, you will see a ‘Command’ text box. Append the following to the command string:
-Output C:\AgentOuput.txt –OutputVerboseLevel 3
- Restart the agent.
- Once the agent completes the execution, you can analyze the contents of this file (In this case C:\AgentOutput.txt). In case of push subscriptions, these files will be created on the distributing SQL Server’s hard disk. In case of pull subscriptions, these files will be created on the subscribing SQL Server’s hard disk.
Additionally, you can use Windows NT Performance Monitor to monitor the replication activity on the server.
(Continued on next question...)