- 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
Distribution Database Stored Procedures
If you know some of the tables in the distribution database, you might be tempted to query or even modify data in those tables. Be aware that Microsoft strongly discourages doing so. Instead, you should usually use stored procedures provided with the distribution database for tracking down a particular issue. Occasionally, if you can't find a stored procedure that queries a particular table in a distribution database, you have no choice but to query the table directly.
Out of numerous procedures included in the distribution database, you're likely to use only a few for monitoring and troubleshooting.
Perhaps the most frequently used procedure is sp_browsereplcmds. This procedure reads records out of the MSRepl_Commands table and displays them in a humanly readable format, rather than the binary format in which they're stored. Although you can limit the output of this procedure by providing parameters, usually you'll be executing it without any parameters. Here is a sample output:
)
Sample
OutoutThe most important pieces of information in this output are the publisher database ID (the database where the original transaction occurred), article id (the article affected by the transaction and command), and the command to be executed on the subscribing databases. The publisher database ID column points to the MSPublisher_databases table, in which you can find the name of the published database. Similarly, you can query MSArticles to get the name of the article associated with a particular article ID. By far the most useful portion of the output is the Command column. If you experience replication errors, you can check the command that is causing the problem and try to execute it manually on the subscribing database. Sp_browsereplcmds also gives you an idea of the transaction volume (total number of commands) your distributor handles at any given point in time.
Other procedures you might find helpful are sp_MSenum_logreader, sp_MSenum_snapshot, and sp_MSenum_distribution. As you might guess, these procedures return information about the Log Reader, Snapshot, and Distribution agents running on the local distribution server. A sample output from sp_MSenum_logreader is shown next:
)
See
Example
The sp_MSEnum_snapshot and sp_MSEnum_distribution procedures return similar information about the Snapshot and Distribution agents, respectively.