sp_refreshsubscriptions - An useful Transactional Replication Stored Procedure | SansSQL

Friday, January 14, 2011

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

4 comments:

Anonymous said...

Usefull posts...hats off dude..

onlinetvmasthi said...

very useful

Igor Chmil said...

As long as immediate_sync is set to true, there is no need to exec sp_refreshsubscriptions. It gets executed by sp_addarticle: "sp_refreshsubscriptions is called by sp_addarticle for an immediate-updating publication"

ref: https://msdn.microsoft.com/en-us/library/ms181680.aspx

Kay said...

Every time I run exec sp_refreshsubscriptions on publication server, deadlock occurred. Can you please let me know how to avoid deadlock issue.

Post a Comment

Ads