- Meet Replication Monitor 2005
- Troubleshooting Replication Errors
- Validating Subscriptions
- Monitoring and Troubleshooting Replication Performance
- System Procedures for Monitoring Replication
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
System Procedures for Monitoring Replication
You can execute several system-stored procedures to monitor replication programmatically or to troubleshoot replication errors. Most commonly you’ll still use the sp_browsereplcmds procedure, much as you did with previous versions of SQL Server. Although you could execute this procedure without any parameters, you can specify beginning and ending transaction sequence number, command id, agent id, transaction originator id, article id, and compatibility level. (I showed you how you can examine replicated commands with sp_browsereplcmds earlier in this article.)
Procedure sp_replmonitorhelppublisher can be used to get information regarding publishers that use the current server as the distributor; this procedure can be executed without any parameters to get information about all publishers or with a publisher name to get information about a single publisher. For example:
EXEC sp_replmonitorhelppublisher
Results:
|
publisher |
distribution_db |
Status |
warning |
publication count |
returnstamp |
|
D10ZF411\SQLSERVER2005 |
Distribution |
4 |
0 |
2 |
2006071219205930 |
The status column can take one of the following values:
- 1 – started
- 2 – succeeded
- 3 – in progress
- 4 – idle
- 5 – retrying
- 6 – failed
The Warning column can take several different values, depending on the status of subscriptions to any publication on this publisher.
You can execute two very similar procedures (sp_replmonitorhelppublication and sp_replmonitorhelpsubscription, both new to SQL Server 2005) in the distribution database to examine the health of a given publication and any associated subscriptions. Please refer to the online documentation for detailed description and sample execution of these procedures.