Adding an article – do I really want a complete snapshot?

Have you ever noticed that when you add an article to a transactional publication and run the snapshot agent it does a complete snapshot of all tables? This can be a real pain for big publications. Fear not – it is configurable. The setting is visible in the Replication Monitor. Actually 2 settings – shown below. We need both to be False but the observant DBA will notice that the main one of these is greyed out!

 

A little scripting solves it though. We just run the script below before adding a table and all is well – the snapshot agent then just creates the new article.

EXEC sp_changepublication
@publication = ‘pubTestTransactional’,
@property = ‘allow_anonymous’,
@value = ‘false’
GO

EXEC sp_changepublication
@publication = ‘pubTestTransactional’,
@property = ‘immediate_sync’,
@value = ‘false’
GO

3 thoughts on “Adding an article – do I really want a complete snapshot?”

  1. But this creates a bigger distribution db. I suggest making them false only when you are going to be adding new articles and true the rest of the time. This saves i/o’s and keeps the distribution db smaller.

  2. Thanks Gene – I never noticed the effect on the distribution database – I’m going to have a play with this and then then reply properly.

Leave a Reply

Your email address will not be published.