Useful transactional replication stored procedure

Posted by

The scenario is when your publication settings has the option
immediate_sync configured as false (means if you run the Snapshot Agent, the snapshot files will be created for the new articles only and also a new subscriber to the existing publication, the snapshot files only get created for the new subscription)

This option immediate_sync will prevent to generate all snapshot files but it still need schema lock to check all articles in the publication.

Continuing with scenario that you have a large number of articles and a bunch of subscribers, you might face the situation you have to add a new article in your transactional replication and after run the snapshot that article is not propagate to the subscribers.

First, check your publication settings.

exec sp_helppublication 'PublicationName'
GO

With immediate_sync and allow_anonymous false you can run the procedure below to solve the issue that your new article didn’t propagate to your subscribers.

exec sp_refreshsubscriptions 'PublicationName'
GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s