sp_refreshsubscriptions - An useful Transactional Replication Stored Procedure

Consider you are having a Transactional replication setup in a Live environment with a huge published database (For example, more than 50 GB publisher database size) , and you want to add an article to this existing publication without generating the snapshot for the all the articles. Also consider that the size of the new article that you are going to add is only 2 MB.:)
Now think, Do you really prefer to generate and apply the snapshot for the complete 50 GB database just for adding a 2 MB article?
I would really not prefer this as the environment is Live and its requires more time to complete the process of generating and applying snapshot. Then how will you achieve this?

You can do this by using the unpopular Transactional Replication Stored Procedure sp_refreshsubscriptions.
This stored proc expects a parameter "Publication Name" and has to be run on the publisher database.
When you run this on the publisher, it will mark only the newly added articles for generating snapshot.

Syntax:

exec sp_refreshsubscriptions 'PublicationName'

GO

Example:

-- Adding the transactional articles

use [AdventureWorks]

exec sp_addarticle @publication = N'TestAdv', @article = N'Contact', @source_owner = N'Person', @source_object = N'Contact', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Contact', @destination_owner = N'Person', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_PersonContact]', @del_cmd = N'CALL [dbo].[sp_MSdel_PersonContact]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_PersonContact]'

GO

--Refresh Subscriptions

exec sp_refreshsubscriptions 'TestAdv'

GO

After running the above commands, run the snapshot agent.

Before running the sp_refreshsubscriptions SP, make sure that the publisher properties "allow_anonymous" and "immediate_sync" are set to "False", if these 2 options are set to "True" then this SP will mark all the articles for generating snapshot instead of marking only the newly added articles.

To Check the publication properties, use this query.

exec sp_helppublication 'PublicationName'
GO

If the values of the output columns "allow_anonymous" and "immediate_sync" are 0 then they are set to "False" if their values are 1 then they are set to "True"

To Change the publication properties for "allow_anonymous" and "immediate_sync", use this query

EXEC sp_changepublication

@publication = 'PublicationName',

@property = N'allow_anonymous',

@value = 'false'

GO

EXEC sp_changepublication

@publication = 'PublicationName',

@property = N'immediate_sync',

@value = 'false'

GO