Replication Stored Procedures (Troubleshooting)
This topic describes a number of replication stored procedures that can be used during troubleshooting.
Procedures for All Types of Replication
Procedure |
Description |
For more information… |
|---|---|---|
sp_addscriptexec |
Posts a Microsoft SQL Server script (.sql file) to all Subscribers of a publication. |
|
sp_adjustpublisheridentityrange |
Adjusts the identity range on a publication and reallocates new ranges based on the threshold value on the publication. |
|
sp_changereplicationserverpasswords |
Changes stored passwords for the Microsoft Windows account or SQL Server login used by replication agents when connecting to servers in a replication topology. You would normally have to change a password for each individual agent running at a server, even if they all use the same login or account. This stored procedure enables you to change the password for all instances of a given SQL Server login or Windows account used by all replication agents that run at a server. |
|
sp_removedbreplication |
Removes all replication objects from a database. This stored procedure is executed at the Publisher on the publication database or at the Subscriber, on the subscription database. When executed at the Publisher on the publication database, an attempt is made to remove objects related to the published database at the Distributor and Subscriber. |
|
sp_removedistpublisherdbreplication |
Removes publishing metadata belonging to a specific publication at the Distributor. |
|
sp_replmonitorhelppublication |
Returns current status information for one or more publications at a Publisher. |
|
sp_replmonitorhelppublicationthresholds |
Returns the threshold metrics set for a monitored publication. |
|
sp_replmonitorhelppublisher |
Returns current status information for one or more Publishers. |
|
sp_replmonitorhelpsubscription |
Returns current status information for subscriptions belonging to one or more publications at the Publisher and returns one row for each returned subscription. |
|
sp_table_validation |
Either returns row count or checksum information on a table or indexed view, or compares the provided row count or checksum information with the specified table or indexed view. |
Procedures for Transactional Replication
Procedure |
Description |
For more information… |
|---|---|---|
sp_article_validation |
Initiates a data validation request for the specified article. |
|
sp_marksubscriptionvalidation |
Marks the current open transaction to be a subscription level validation transaction for the specified Subscriber. |
|
sp_publication_validation |
Initiates an article validation request for each article in the specified publication. |
|
sp_browsereplcmds |
Returns a result set in a readable version of the replicated commands stored in the distribution database. |
|
sp_helppeerrequests |
Returns information on all status requests received by participants in a peer-to-peer replication topology, where these requests were initiated by executing sp_requestpeerresponse at any published database in the topology. |
|
sp_helppeerresponses |
Returns all responses to a specific status request received from a participant in a peer-to-peer replication topology, where the request was initiated by executing sp_requestpeerresponse at any published database in the topology. |
|
sp_requestpeerresponse |
When executed from a node in a peer-to-peer topology, this procedure requests a response from every other node in the topology. |
|
sp_deletepeerrequesthistory |
Deletes history related to a publication status request in a peer-to-peer replication topology. |
|
sp_posttracertoken |
This procedure posts a tracer token into the transaction log at the Publisher and begins the process of tracking latency statistics. Information is recorded when the tracer token is written to the transaction log, when it is picked up by the Log Reader Agent, and when it is applied by the Distribution Agent. |
|
sp_helptracertokens |
Returns one row for each tracer token that has been inserted into a publication to determine latency. |
|
sp_helptracertokenhistory |
Returns detailed latency information for specified tracer tokens, with one row being returned for each Subscriber. |
|
sp_deletetracertokenhistory |
Removes tracer token records from the MStracer_tokens and MStracer_history system tables. |
|
sp_replcmds |
This procedure is used by the Log Reader Agent. It returns information about the publication database from which it is executed. It allows you to view transactions that currently are not distributed (those transactions remaining in the transaction log that have not been sent to the Distributor). |
|
sp_replcounters |
Returns replication statistics about latency, throughput, and transaction count for each published database. |
|
sp_repldone |
Updates the record that identifies the last distributed transaction of the server. |
|
sp_replflush |
Article definitions are stored in the cache for efficiency. This procedure is used by other replication stored procedures whenever an article definition is modified or dropped. |
|
sp_replshowcmds |
Returns the commands for transactions marked for replication in readable format. |
|
sp_repltrans |
Returns a result set of all the transactions in the publication database transaction log that are marked for replication but have not been marked as distributed. |
|
sp_setsubscriptionxactseqno |
Used to specify the log sequence number (LSN) of the next transaction to be applied by the Distribution Agent at the Subscriber, which enables the agent to skip a failed transaction. |
|
sp_helpsubscriptionerrors |
Returns all transactional replication errors for a given subscription. |
|
sp_replmonitorsubscriptionpendingcmds |
Returns information on the number of pending commands for a subscription to a transactional publication and an estimate of how much time it takes to process them. |
|
sp_replqueuemonitor |
Lists the queue messages for queued updating subscriptions. |
Procedures for Merge Replication
Procedure |
Description |
For more information… |
|---|---|---|
sp_showpendingchanges |
Returns a result set showing an approximate number of changes that are waiting to be replicated. |
|
sp_showrowreplicainfo |
Displays information about a row in a table that is being used as an article in merge replication. |
|
sp_enumeratependingschemachanges |
Returns a list of all pending schema changes. This stored procedure can be used with sp_markpendingschemachange. |
|
sp_markpendingschemachange |
Enables an administrator to skip selected pending schema changes so that they are not replicated. |
|
sp_addtabletocontents |
Inserts references into the merge tracking tables for any rows in a source table that are not currently included in the tracking tables. |
|
sp_deletemergeconflictrow |
Deletes rows from merge conflict tables. |
|
sp_helpmergearticleconflicts |
Returns the articles in the publication that have conflicts. |
|
sp_helpmergeconflictrows |
Returns the rows in the specified conflict table. |
|
sp_helpmergedeleteconflictrows |
Returns information on data rows that lost delete conflicts. |
|
sp_mergemetadataretentioncleanup |
Performs a manual cleanup of metadata in the MSmerge_genhistory, MSmerge_contents and MSmerge_tombstone system tables. |
|
sp_replmonitorhelpmergesession |
Returns information on past sessions for a given replication Merge Agent. |
|
sp_replmonitorhelpmergesessiondetail |
Returns detailed, article-level information on a specific replication Merge Agent session. |
|
sp_validatemergepublication |
Performs a publication-wide validation. |
|
sp_validatemergesubscription |
Performs a validation for the specified subscription. |