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_addscriptexec (Transact-SQL)

sp_adjustpublisheridentityrange

Adjusts the identity range on a publication and reallocates new ranges based on the threshold value on the publication.

sp_adjustpublisheridentityrange (Transact-SQL)

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_changereplicationserverpasswords (Transact-SQL)

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_removedbreplication (Transact-SQL)

sp_removedistpublisherdbreplication

Removes publishing metadata belonging to a specific publication at the Distributor.

sp_removedistpublisherdbreplication (Transact-SQL)

sp_replmonitorhelppublication

Returns current status information for one or more publications at a Publisher.

sp_replmonitorhelppublication (Transact-SQL)

sp_replmonitorhelppublicationthresholds

Returns the threshold metrics set for a monitored publication.

sp_replmonitorhelppublicationthresholds (Transact-SQL)

sp_replmonitorhelppublisher

Returns current status information for one or more Publishers.

sp_replmonitorhelppublisher (Transact-SQL)

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_replmonitorhelpsubscription (Transact-SQL)

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.

sp_table_validation (Transact-SQL)

Procedures for Transactional Replication

Procedure

Description

For more information…

sp_article_validation

Initiates a data validation request for the specified article.

sp_article_validation (Transact-SQL)

sp_marksubscriptionvalidation

Marks the current open transaction to be a subscription level validation transaction for the specified Subscriber.

sp_marksubscriptionvalidation (Transact-SQL)

sp_publication_validation

Initiates an article validation request for each article in the specified publication.

sp_publication_validation (Transact-SQL)

sp_browsereplcmds

Returns a result set in a readable version of the replicated commands stored in the distribution database.

sp_browsereplcmds (Transact-SQL)

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_helppeerrequests (Transact-SQL)

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_helppeerresponses (Transact-SQL)

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_requestpeerresponse (Transact-SQL)

sp_deletepeerrequesthistory

Deletes history related to a publication status request in a peer-to-peer replication topology.

sp_deletepeerrequesthistory (Transact-SQL)

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_posttracertoken (Transact-SQL)

sp_helptracertokens

Returns one row for each tracer token that has been inserted into a publication to determine latency.

sp_helptracertokens (Transact-SQL)

sp_helptracertokenhistory

Returns detailed latency information for specified tracer tokens, with one row being returned for each Subscriber.

sp_helptracertokenhistory (Transact-SQL)

sp_deletetracertokenhistory

Removes tracer token records from the MStracer_tokens and MStracer_history system tables.

sp_deletetracertokenhistory (Transact-SQL)

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_replcmds (Transact-SQL)

sp_replcounters

Returns replication statistics about latency, throughput, and transaction count for each published database.

sp_replcounters (Transact-SQL)

sp_repldone

Updates the record that identifies the last distributed transaction of the server.

sp_repldone (Transact-SQL)

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_replflush (Transact-SQL)

sp_replshowcmds

Returns the commands for transactions marked for replication in readable format.

sp_replshowcmds (Transact-SQL)

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_repltrans (Transact-SQL)

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_setsubscriptionxactseqno (Transact-SQL)

sp_helpsubscriptionerrors

Returns all transactional replication errors for a given subscription.

sp_helpsubscriptionerrors (Transact-SQL)

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_replmonitorsubscriptionpendingcmds (Transact-SQL)

sp_replqueuemonitor

Lists the queue messages for queued updating subscriptions.

sp_replqueuemonitor (Transact-SQL)

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_showpendingchanges (Transact-SQL)

sp_showrowreplicainfo

Displays information about a row in a table that is being used as an article in merge replication.

sp_showrowreplicainfo (Transact-SQL)

sp_enumeratependingschemachanges

Returns a list of all pending schema changes. This stored procedure can be used with sp_markpendingschemachange.

sp_enumeratependingschemachanges (Transact-SQL)

sp_markpendingschemachange

Enables an administrator to skip selected pending schema changes so that they are not replicated.

sp_markpendingschemachange (Transact-SQL)

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_addtabletocontents (Transact-SQL)

sp_deletemergeconflictrow

Deletes rows from merge conflict tables.

sp_deletemergeconflictrow (Transact-SQL)

sp_helpmergearticleconflicts

Returns the articles in the publication that have conflicts.

sp_helpmergearticleconflicts (Transact-SQL)

sp_helpmergeconflictrows

Returns the rows in the specified conflict table.

sp_helpmergeconflictrows (Transact-SQL)

sp_helpmergedeleteconflictrows

Returns information on data rows that lost delete conflicts.

sp_helpmergedeleteconflictrows (Transact-SQL)

sp_mergemetadataretentioncleanup

Performs a manual cleanup of metadata in the MSmerge_genhistory, MSmerge_contents and MSmerge_tombstone system tables.

sp_mergemetadataretentioncleanup (Transact-SQL)

sp_replmonitorhelpmergesession

Returns information on past sessions for a given replication Merge Agent.

sp_replmonitorhelpmergesession (Transact-SQL)

sp_replmonitorhelpmergesessiondetail

Returns detailed, article-level information on a specific replication Merge Agent session.

sp_replmonitorhelpmergesessiondetail (Transact-SQL)

sp_validatemergepublication

Performs a publication-wide validation.

sp_validatemergepublication (Transact-SQL)

sp_validatemergesubscription

Performs a validation for the specified subscription.

sp_validatemergesubscription (Transact-SQL)