Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Marks the subscription for reinitialization. This stored procedure is executed at the Publisher for push subscriptions.
sp_reinitsubscription [ [ @publication = ] 'publication' ] [ , [ @article = ] 'article' ] , [ @subscriber = ] 'subscriber' [ , [ @destination_db = ] 'destination_db'] [ , [ @for_schema_change = ] 'for_schema_change'] [ , [ @publisher = ] 'publisher' ] [ , [ @ignore_distributor_failure = ] ignore_distributor_failure ] [ , [ @invalidate_snapshot = ] invalidate_snapshot ]
sp_reinitsubscription is used in transactional replication.
sp_reinitsubscription is not supported for peer-to-peer transactional replication.
For subscriptions where the initial snapshot is applied automatically and where the publication does not allow updatable subscriptions, the Snapshot Agent must be run after this stored procedure is executed so that schema and bulk copy program files are prepared and the Distribution Agents is then able to resynchronize the subscriptions.
For subscriptions where the initial snapshot is applied automatically and the publication allows updatable subscriptions, the Distribution Agent resynchronizes the subscription using the most recent schema and bulk copy program files previously created by the Snapshot Agent. The Distribution Agent resynchronizes the subscription immediately after the user executes sp_reinitsubscription, if the Distribution Agent is not busy; otherwise, synchronization may occur after the message interval (specified by Distribution Agent command-prompt parameter: MessageInterval).
sp_reinitsubscription has no effect on subscriptions where the initial snapshot is applied manually.
To resynchronize anonymous subscriptions to a publication, pass in all or NULL as subscriber.
Transactional replication supports subscription reinitialization at the article level. The snapshot of the article is reapplied at the Subscriber during the next synchronization after the article is marked for reinitialization. However, if there are dependent articles that are also subscribed to by the same Subscriber, reapplying the snapshot on the article might fail unless dependent articles in the publication are also automatically reinitialized under certain circumstances:
If the pre-creation command on the article is 'drop', articles for schema-bound views and schema-bound stored procedures on the base object of that article is marked for reinitialization as well.
If the schema option on the article includes scripting of declared referential integrity on the primary keys, articles that have base tables with foreign key relationships to base tables of the reinitialized article are marked for reinitialization as well.
-- This script uses sqlcmd scripting variables. They are in the form -- $(MyVariable). For information about how to use scripting variables -- on the command line and in SQL Server Management Studio, see the -- "Executing Replication Scripts" section in the topic -- "Programming Replication Using System Stored Procedures". DECLARE @subscriptionDB AS sysname; DECLARE @publication AS sysname; SET @subscriptionDB = N'AdventureWorks2012Replica'; SET @publication = N'AdvWorksProductTran'; USE [AdventureWorks2012Replica] -- Execute at the Publisher to reinitialize the push subscription. EXEC sp_reinitsubscription @subscriber = $(SubServer), @destination_db = @subscriptionDB, @publication = @publication; GO -- Start the Distribution Agent.