Home > Articles > Data > SQL Server

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account