I'm using SQL Server 2014 SP3CU2 Enterprise Edition.

I have a transaction replication on a DB with some very large tables. I needed to change the structure of a column on one of the tables that were replicated (1.8B rows), so I did the following:

  1. Created a duplicated table with the new structure.
  2. Removed the table (article) from the publication

EXEC sp_dropsubscription @publication = N'PubName', @article = N'TableName', @subscriber = N'SubscriberName', @destination_db = N'DB';

EXEC sp_droparticle @publication = N'PubName', @ article = N'TableName'

  1. Switched the tables (old/new)
  2. Added the table (article) to the publication

EXEC sp_addarticle @publication = N'PubName', > @article = N'TableName', @source_owner = N'SchemaName', @source_object = N'TableName', @type = N'logbased', @description = N'', @creation_script = N'' , @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'TableName', @destination_owner = N'SchameName', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_SchemaNameTableName]', @del_cmd = N'CALL [sp_MSdel_SchemaNameTableName], @upd_cmd = N'SCALL [sp_MSupd_SchemaNameTableName]'

exec sp_addsubscription @publication = N'PubName', @subscriber = N'SubscriberName@destination_db = N'DB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

  1. Started the Snapshot agent.

I did it in the ACC environment with no issues (that env is much smaller though).

In prod, the snapshot agent took 2.5h to generate the 1 article (as expected, as it's a large table), but once it was done, the distribution agent just filled the gap - the missing rows on the subscriber and continued the sync - the table WASN'T dropped on the subscriber side.

I'm puzzled and can't understand why was it acting this way.

I'm planning to do this again in a few weeks - to re-initialized the replication (because I need to coordinate it with other people) but I can't explain what happened and/or what I need to do in order to avoid it from happening again.

Any help will be appreciated.

Thanks in advance, Roni.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.