Replication stored procedures (Transact-SQL)

Applies to: SQL Server Azure SQL Database

Replication system stored procedures are documented and available as a method for accomplishing one-time tasks, such as implementing replication, and for using in batch files and scripts.

For more information on how to program most of the common replication tasks using replication stored procedures, see Replication System Stored Procedures Concepts.

Important

Only the replication stored procedures documented in SQL Server Books Online are supported. Undocumented stored procedures are only for the use of internal replication components and shouldn't be used to administer replication.

Publication and distribution setup

These procedures configure the replication infrastructure, including Distributors, Publishers, and distribution databases.

Stored procedure	Description
sp_adddistpublisher	Registers a Publisher at a Distributor and specifies the default snapshot folder for replication.
sp_adddistributor	Configures a server as a Distributor for replication, specifying the password for the distributor_admin account.
sp_adddistributiondb	Creates the distribution database on the Distributor with configurable retention periods.
sp_addlogreader_agent	Creates the Log Reader Agent job for a Transactional publication.
sp_addpublication	Creates a new Snapshot or Transactional publication in the publication database.
sp_addpublication_snapshot	Creates the Snapshot Agent job for a publication.
sp_addqreader_agent	Creates the Queue Reader Agent job for a Distributor that supports queued updating subscriptions.
sp_changedistpublisher	Changes the properties of a Publisher registered at the Distributor.
sp_changedistributiondb	Modifies properties of the distribution database, including retention periods.
sp_changedistributor_password	Changes the password used for the connection between the Publisher and a remote Distributor.
sp_changedistributor_property	Modifies Distributor properties such as the heartbeat interval for agent status checks.
sp_changepublication	Modifies properties of a Snapshot or Transactional publication.
sp_changepublication_snapshot	Changes the security credentials or scheduling properties of the Snapshot Agent.
sp_changelogreader_agent	Changes the security properties of the Log Reader Agent.
sp_changeqreader_agent	Changes the security properties for the Queue Reader Agent.
sp_changereplicationserverpasswords	Changes stored passwords for the Windows account or SQL Server login used by replication agents when connecting to servers in a replication topology.
sp_dropdistpublisher	Removes a Publisher from the Distributor.
sp_dropdistributiondb	Drops a distribution database and its associated replication jobs.
sp_dropdistributor	Drops the Distributor by removing the distribution configuration.
sp_droppublication	Drops a publication and all articles associated with it.
sp_get_distributor	Returns the Distributor installed on a server.
sp_get_redirected_publisher	Returns the redirected Publisher for an availability group publisher database.
sp_grant_publication_access	Adds a login to the publication access list.
sp_helpdistpublisher	Returns properties of Publishers registered at a Distributor.
sp_helpdistributiondb	Returns properties of a specified distribution database.
sp_helpdistributor	Returns information about the Distributor, distribution database, and working directory.
sp_helpdistributor_properties	Returns Distributor properties such as heartbeat interval.
sp_helplogreader_agent	Returns properties of the Log Reader Agent job.
sp_helppublication	Returns information about a publication.
sp_helppublication_snapshot	Returns information about the Snapshot Agent job for a publication.
sp_helpqreader_agent	Returns properties of the Queue Reader Agent.
sp_helpreplicationdboption	Returns whether replication is enabled for a database.
sp_helpreplicationoption	Returns replication options enabled on a server.
sp_publisherproperty	Gets or sets Publisher properties for a non-SQL Server Publisher.
sp_redirect_publisher	Specifies a redirected Publisher for an availability group publisher database.
sp_replicationdboption	Enables or disables replication publishing options for a database.
sp_revoke_publication_access	Removes a login from the publication access list.
sp_validate_redirected_publisher	Validates the redirected Publisher for an availability group.
sp_validate_replica_hosts_as_publishers	Validates all replica hosts in an availability group for use as publishers.

Replication article management

These procedures define which database objects are published and configure article properties such as filtering.

Stored procedure	Description
sp_addarticle	Adds an article (table, view, or stored procedure) to a Snapshot or Transactional publication.
sp_articlecolumn	Defines which columns to include or exclude from a published article for vertical filtering.
sp_articlefilter	Creates a row filter for an article using a WHERE clause.
sp_articleview	Creates the synchronization view for a filtered article.
sp_changearticle	Modifies properties of an existing article in a publication.
sp_changearticlecolumndatatype	Changes the column data type mapping for an Oracle publication article.
sp_check_dynamic_filters	Checks whether a Merge publication uses parameterized row filters.
sp_check_for_sync_trigger	Determines if a trigger is being called in the context of synchronization.
sp_check_join_filter	Validates a join filter between two tables to ensure it can be precomputed.
sp_check_subset_filter	Validates a parameterized row filter clause to ensure it's valid.
sp_droparticle	Removes an article from a Snapshot or Transactional publication.
sp_generatefilters	Creates filters for child articles based on foreign key relationships.
sp_helparticle	Displays properties of articles in a publication.
sp_helparticlecolumns	Returns all columns in the underlying base table for an article.
sp_helparticledts	Returns information about custom task names used with DTS subscriptions.

Subscription management

These procedures create, modify, and remove subscriptions to publications, including push and pull subscriptions.

Stored procedure	Description
sp_addsubscription	Creates a subscription to a Snapshot or Transactional publication at the Publisher.
sp_addsubscriber	Registers a server as a Subscriber and configures Subscriber properties.
sp_addsubscriber_schedule	Adds a schedule for the Distribution Agent or Merge Agent.
sp_addpullsubscription	Creates a pull subscription at the Subscriber for a Snapshot or Transactional publication.
sp_addpullsubscription_agent	Creates the Distribution Agent job for a pull subscription at the Subscriber.
sp_addpushsubscription_agent	Creates the Distribution Agent job for a push subscription at the Distributor.
sp_addqueued_artinfo	Adds article information to the queue used by queued updating subscriptions.
sp_addscriptexec	Posts a SQL script to be executed at all Subscribers to a publication.
sp_addsynctriggers	Creates synchronization triggers at the Subscriber for updatable subscriptions.
sp_attachsubscription	Attaches an existing subscription database to any Subscriber.
sp_change_subscription_properties	Changes security information and settings for a pull subscription.
sp_changesubscriber	Changes options for a Subscriber, including connection settings and schedules.
sp_changesubscriber_schedule	Changes the Distribution Agent or Merge Agent schedule for a Subscriber.
sp_changesubscription	Modifies properties of a Snapshot or Transactional subscription.
sp_changesubscriptiondtsinfo	Changes the DTS package properties of a transactional push subscription.
sp_changesubstatus	Changes the status of an existing subscription at the Publisher.
sp_copysubscription	Copies a subscription database that has pull subscriptions but no push subscriptions.
sp_droppullsubscription	Drops a pull subscription at the Subscriber database.
sp_dropsubscriber	Removes the Subscriber designation from a registered server.
sp_dropsubscription	Removes a subscription to a Snapshot or Transactional publication.
sp_expired_subscription_cleanup	Removes expired anonymous subscriptions from publications.
sp_getqueuedrows	Returns rows from the Subscriber that have pending updates in the queue.
sp_getsubscriptiondtspackagename	Returns the DTS package name for a subscription.
sp_helpsubscriberinfo	Returns information about a Subscriber.
sp_helpsubscription	Returns subscription information for a publication.
sp_helpsubscription_properties	Returns security information for a subscription.
sp_helppullsubscription	Returns information about pull subscriptions at the Subscriber.
sp_link_publication	Sets the configuration for immediate updating subscriptions when connecting to the Publisher.
sp_refreshsubscriptions	Adds subscriptions to newly added articles for existing pull subscriptions.
sp_reinitpullsubscription	Marks a pull subscription for reinitialization when the Distribution Agent next runs.
sp_reinitsubscription	Marks a push subscription for reinitialization.
sp_setreplfailovermode	Sets the failover mode for an updatable subscription.
sp_helpreplfailovermode	Returns the current failover mode of a subscription.
sp_subscription_cleanup	Removes metadata when a subscription is dropped from a Subscriber.

Merge replication

These procedures are specific to merge replication, including Merge publications, articles, subscriptions, and conflict handling.

Stored procedure	Description
sp_addmergealternatepublisher	Adds the ability for Subscribers to use an alternate synchronization partner.
sp_addmergearticle	Adds an article to a Merge publication with options for filtering and conflict resolution.
sp_addmergefilter	Defines a join filter or logical record relationship between articles in a Merge publication.
sp_addmergepartition	Creates a partition for parameterized row filters to enable pre-generated snapshots.
sp_addmergepublication	Creates a new Merge publication with options for parameterized filters.
sp_addmergepullsubscription	Creates a pull subscription at the Subscriber for a Merge publication.
sp_addmergepullsubscription_agent	Creates the Merge Agent job for a pull subscription at the Subscriber.
sp_addmergepushsubscription_agent	Creates the Merge Agent job for a push subscription at the Distributor.
sp_addmergesubscription	Creates a push or pull subscription to a Merge publication.
sp_browsemergesnapshotfolder	Returns the path to the most recent snapshot generated for a Merge publication.
sp_changemergearticle	Modifies properties of an existing article in a Merge publication.
sp_changemergefilter	Modifies an existing join filter or logical record relationship.
sp_changemergepublication	Modifies properties of a Merge publication.
sp_changemergepullsubscription	Changes the properties of a merge pull subscription.
sp_changemergesubscription	Changes properties of a merge push subscription.
sp_copymergesnapshot	Copies the snapshot folder to an alternate folder.
sp_deletemergeconflictrow	Deletes rows from a merge conflict table.
sp_dropmergealternatepublisher	Removes an alternate Publisher from a Merge publication.
sp_dropmergearticle	Removes an article from a Merge publication.
sp_dropmergefilter	Drops a join filter from a Merge publication.
sp_dropmergepartition	Removes a partition definition from a Merge publication with parameterized filters.
sp_dropmergepublication	Removes a Merge publication and its associated Snapshot Agent.
sp_dropmergepullsubscription	Drops a Merge pull subscription at the Subscriber database.
sp_dropmergesubscription	Drops a subscription to a Merge publication and removes the associated Merge Agent.
sp_getmergedeletetype	Returns the type of merge delete operation.
sp_helpmergealternatepublisher	Returns a list of servers configured as alternate Publishers.
sp_helpmergearticle	Returns properties of articles in a Merge publication.
sp_helpmergearticlecolumn	Returns the list of columns in a Merge publication article.
sp_helpmergearticleconflicts	Returns conflict table information for articles that have experienced conflicts.
sp_helpmergeconflictrows	Returns the rows in the specified conflict table.
sp_helpmergedeleteconflictrows	Returns data rows that lost delete conflicts.
sp_helpmergefilter	Returns information about merge filters.
sp_helpmergepartition	Returns partition information for a Merge publication.
sp_helpmergepublication	Returns information about a Merge publication.
sp_helpmergepullsubscription	Returns information about Merge pull subscriptions.
sp_helpmergesubscription	Returns information about Merge subscriptions.
sp_mergearticlecolumn	Partitions a Merge publication vertically by filtering columns.
sp_mergecleanupmetadata	Cleans up metadata in system tables after maintenance involving retention periods.
sp_mergedummyupdate	Marks a row for resending during the next merge synchronization.
sp_mergemetadataretentioncleanup	Manually cleans up metadata in system tables based on retention periods.
sp_mergesubscription_cleanup	Removes metadata after a merge push subscription is dropped.
sp_reinitmergepullsubscription	Marks a merge pull subscription for reinitialization.
sp_reinitmergesubscription	Marks a merge subscription for reinitialization.
sp_resyncmergesubscription	Resynchronizes a merge subscription to a known validation state.
sp_restoremergeidentityrange	Updates identity range assignments after restoring a database.
sp_showpendingchanges	Returns the pending changes waiting to be replicated.
sp_showrowreplicainfo	Shows tracking information about a row in a merge article.

Replication agent profiles

These procedures manage agent profiles, which define the parameters used by replication agents.

Stored procedure	Description
sp_add_agent_parameter	Adds a new parameter to an existing replication agent profile.
sp_add_agent_profile	Creates a new agent profile for a replication agent type.
sp_change_agent_parameter	Changes the value of an existing parameter in an agent profile.
sp_change_agent_profile	Modifies properties of an existing replication agent profile.
sp_drop_agent_parameter	Removes a parameter from a replication agent profile.
sp_drop_agent_profile	Deletes a user-defined replication agent profile.
sp_dropanonymousagent	Drops an anonymous agent created for pull subscription monitoring.
sp_getagentparameterlist	Returns the list of valid parameters for a replication agent type.
sp_help_agent_default	Retrieves the default profile ID for the specified agent type.
sp_help_agent_parameter	Returns all parameters of a specified agent profile.
sp_help_agent_profile	Returns information about replication agent profiles.
sp_update_agent_profile	Updates the properties of a replication agent profile.

Snapshot management

These procedures manage snapshot generation and delivery for replication.

Stored procedure	Description
sp_adddynamicsnapshot_job	Creates a Snapshot Agent job that generates a filtered data snapshot for a Merge publication partition.
sp_browsesnapshotfolder	Returns the path to the most recent snapshot generated for a publication.
sp_changedynamicsnapshot_job	Changes security settings for the filtered snapshot job for a Merge publication partition.
sp_copysnapshot	Copies the snapshot folder to the specified folder.
sp_dropdynamicsnapshot_job	Removes the parameterized snapshot job for a Merge publication partition.
sp_helpdynamicsnapshot_job	Returns information about parameterized snapshot jobs.
sp_resetsnapshotdeliveryprogress	Resets the snapshot delivery process for a pull subscription so it can be restarted.
sp_startpublication_snapshot	Starts the Snapshot Agent job for a publication.

Monitoring and validation

These procedures monitor replication performance and validate data consistency.

Stored procedure	Description
sp_article_validation	Initiates validation (row count or checksum) for a single article.
sp_browsereplcmds	Returns a result set of replicated commands stored in the distribution database.
sp_deletetracertokenhistory	Removes tracer token history records from the distribution database.
sp_deletepeerrequesthistory	Deletes request history for peer-to-peer replication status requests.
sp_helptracertokenhistory	Returns detailed latency information for tracer tokens.
sp_helptracertokens	Returns information about tracer tokens that have been inserted into a publication.
sp_helpsubscriptionerrors	Returns errors for transactional replication stored in the distribution database.
sp_marksubscriptionvalidation	Marks the current open transaction as a subscription-level validation transaction.
sp_posttracertoken	Inserts a tracer token into the transaction log to measure replication latency.
sp_publication_validation	Initiates validation for all articles in a Transactional publication.
sp_replcounters	Returns replication statistics for each published database.
sp_replmonitorchangepublicationthreshold	Changes the monitoring threshold metric for a publication.
sp_replmonitorhelpmergesession	Returns information about past Merge Agent sessions.
sp_replmonitorhelpmergesessiondetail	Returns detailed, article-level information about a Merge Agent session.
sp_replmonitorhelppublication	Returns monitoring information about publications at the Distributor.
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 monitoring information about subscriptions.
sp_replmonitorsubscriptionpendingcmds	Returns the number of pending commands for a subscription.
sp_replqueuemonitor	Returns queue messages from the queue for a queued updating subscription.
sp_table_validation	Performs row count or checksum validation on a specified table.
sp_validatemergepublication	Marks all subscriptions to a Merge publication for validation.
sp_validatemergesubscription	Marks a specific Merge subscription for validation.

Peer-to-peer replication

These procedures are specific to peer-to-peer transactional replication.

Stored procedure	Description
sp_configure_peerconflictdetection	Configures conflict detection for a peer-to-peer Transactional publication.
sp_gettopologyinfo	Returns information about the peer-to-peer replication topology.
sp_help_peerconflictdetection	Returns information about the conflict detection setting for a publication.
sp_helppeerrequests	Returns information about status requests received in a peer-to-peer topology.
sp_helppeerresponses	Returns all responses to a status request in a peer-to-peer topology.
sp_requestpeerresponse	Requests a response from every other node in a peer-to-peer topology.
sp_requestpeertopologyinfo	Gathers topology information about a peer-to-peer transactional replication topology.

Schema changes and maintenance

These procedures handle schema changes, scripting, and maintenance operations.

Stored procedure	Description
sp_addtabletocontents	Inserts tracking references for rows not currently included in merge tracking tables.
sp_adjustpublisheridentityrange	Adjusts the identity range on a publication and reallocates new ranges based on the threshold.
sp_dsninfo	Returns ODBC or OLE DB data source information from the Distributor.
sp_enumcustomresolvers	Returns a list of all available business logic handlers and custom resolvers.
sp_enumdsn	Returns a list of ODBC and OLE DB data source names defined for the server.
sp_enumeratependingschemachanges	Returns a list of all pending schema changes.
sp_getdefaultdatatypemapping	Returns the default mapping between SQL Server and a non-SQL Server database type.
sp_helpdatatypemap	Returns information about defined data type mappings between SQL Server and non-SQL Server databases.
sp_helpxactsetjob	Returns the job schedule for the Xactset job.
sp_ivindexhasnullcols	Validates that a clustered index on an indexed view has no nullable columns.
sp_lookupcustomresolver	Returns information on a business logic handler or custom resolver.
sp_markpendingschemachange	Marks selected pending schema changes so they won't be replicated.
sp_register_custom_scripting	Registers a stored procedure to execute when a schema change occurs.
sp_registercustomresolver	Registers a business logic handler or custom resolver for merge replication.
sp_removedbreplication	Removes all replication objects from a database.
sp_removedistpublisherdbreplication	Removes publishing metadata at the Distributor.
sp_repladdcolumn	Adds a column to an existing table article that has been published.
sp_replcmds	Returns commands for transactions marked for replication from the log.
sp_repldone	Updates the record that identifies the server's last distributed transaction.
sp_repldropcolumn	Drops a column from an existing table article that has been published.
sp_replflush	Flushes the article cache.
sp_replication_agent_checkup	Checks each distribution database for replication agents that have no logged history.
sp_replrestart	Resets remote transactional replication.
sp_replsetoriginator	Prevents loopback triggering in bidirectional transactional replication.
sp_replshowcmds	Returns the commands for transactions marked for replication in readable format.
sp_repltrans	Returns all the transactions pending replication in the publication database log.
sp_restoredbreplication	Removes replication settings when restoring a database to a non-originating server.
sp_schemafilter	Modifies the schema filter used when listing Oracle tables eligible for publishing.
sp_script_synctran_commands	Generates a script for sp_addsynctriggers calls to apply at Subscribers.
sp_scriptdynamicupdproc	Generates the CREATE PROCEDURE statement for dynamic update stored procedures.
sp_scriptpublicationcustomprocs	Scripts the custom procedures for all table articles in a publication.
sp_scriptsubconflicttable	Generates script for creating a conflict table at the Subscriber.
sp_setdefaultdatatypemapping	Marks an existing data type mapping as the default.
sp_setsubscriptionxactseqno	Specifies the last delivered transaction for troubleshooting.
sp_unregister_custom_scripting	Removes a user-defined custom stored procedure or script file registered for schema changes.
sp_unregistercustomresolver	Unregisters a business logic module from merge replication.
sp_vupgrade_mergeobjects	Regenerates article-specific triggers, stored procedures, and views for merge replication.
sp_vupgrade_replication	Upgrades replication metadata when upgrading SQL Server.

Microsoft-internal procedures

These procedures modify agent properties at the Distributor and are used internally by replication agents.

Stored procedure	Description
sp_MSchange_distribution_agent_properties	Changes the properties of a Distribution Agent job running at the Distributor.
sp_MSchange_logreader_agent_properties	Changes the properties of a Log Reader Agent job running at the Distributor.
sp_MSchange_merge_agent_properties	Changes the properties of a Merge Agent job running at the Distributor.
sp_MSchange_snapshot_agent_properties	Changes the properties of a Snapshot Agent job running at the Distributor.

Related content

• Replication Management Objects Concepts
• Replication Programming Concepts