transactional replication @force_invalidate_snapshot = 1 parameter

Question

transactional replication @force_invalidate_snapshot = 1 parameter

DBA-640728

SSChampion

Points: 12898
More actions
May 25, 2018 at 11:45 am

#344807

hello, I have transactional replication set up in my enterprise sql 2014 server. Publication has IMMEDIATE_SYNC and ALLOW_ANONYMOUS properties set to FALSE, sync_method= concurrent. I added an article using script below .
EXEC sp_addarticle
@publication = 'testrepl',
@article = 'test',
@source_object = 'testrepl2'
exec sp_refreshsubscriptions N'Pub1'
exec sp_startpublication_snapshot N'Pub1'
I have used this same script several times in the past with no issues. Objects created in the subscriber and snapshot was only run for new articles.
his time, eventhough snapshot ran with no errors for this new article, it did not create the table or objects at the subscriber.
I researched and I dropped the new created article and ran same script only difference i added this time the parameter @force_invalidate_snapshot
EXEC sp_addarticle
@publication = 'testrepl',
@article = 'testrepl2',
@source_object = 'testrepl2',
@force_invalidate_snapshot = 1
Adding article with this parameter worked. it only run the snapshot for new article.
My question is why in which case is this parameter needed? can it cause the snapshot for all the articles to run? I have ran add article script without this parameter several times before with no issues in the past. I also read a few cases that when running it with this parameter it causes snapshot to run for all articles instead of only new article added. Can you guys help?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

DBA-640728 - Friday, May 25, 2018 11:45 AM
hello, I have transactional replication set up in my enterprise sql 2014 server. Publication has IMMEDIATE_SYNC and ALLOW_ANONYMOUS properties set to FALSE, sync_method= concurrent. I added an article using script below .
EXEC sp_addarticle
='font-size:10.5pt;font-family:"helvetica",sans-serif;mso-fareast-font-family:"times>='font-size:10.0pt;font-family:"courier>
@publication = 'testrepl',
='font-size:10.5pt;font-family:"helvetica",sans-serif;mso-fareast-font-family:"times>='font-size:10.0pt;font-family:"courier>
@article = 'test',
='font-size:10.5pt;font-family:"helvetica",sans-serif;mso-fareast-font-family:"times>='font-size:10.0pt;font-family:"courier>
@source_object = 'testrepl2'
exec sp_refreshsubscriptions N'Pub1'
exec sp_startpublication_snapshot N'Pub1'
I have used this same script several times in the past with no issues. Objects created in the subscriber and snapshot was only run for new articles.
his time, eventhough snapshot ran with no errors for this new article, it did not create the table or objects at the subscriber.
I researched and I dropped the new created article and ran same script only difference i added this time the parameter @force_invalidate_snapshot
EXEC sp_addarticle
@publication = 'testrepl',
@article = 'testrepl2',
@source_object = 'testrepl2',
@force_invalidate_snapshot = 1
Adding article with this parameter worked. it only run the snapshot for new article.
My question is why in which case is this parameter needed? can it cause the snapshot for all the articles to run? I have ran add article script without this parameter several times before with no issues in the past. I also read a few cases that when running it with this parameter it causes snapshot to run for all articles instead of only new article added. Can you guys help?
='font-size:10.0pt;font-family:"courier>

You had an available snapshot so you needed to invalidated the one that was available. The previous times there likely was no snapshot available. It doesn't affect what would be in a snapshot when adding an article. The most important pieces when adding an article and generating a snapshot for just that article is making sure @immediate_sync and @allow_anonymous are both false (or 0).

Sue

DBA-640728 SSChampion Points: 12898 More actions · Answer 2

hi, thanks for you reply Sue, I don't think I understand fully, how did I have an available snapshot already? how can I check that so it doesn't happen again?

Sue_H SSC Guru Points: 90860 More actions · Answer 3

DBA-640728 - Tuesday, May 29, 2018 2:47 PM
hi, thanks for you reply Sue, I don't think I understand fully, how did I have an available snapshot already? how can I check that so it doesn't happen again?

Initially a snapshot it created (unless initialized from a backup). It can also be recreated when reinitializing or you can just generate a snapshot
The snapshot files are in the share setup for replication.

Sue

DBA-640728 SSChampion Points: 12898 More actions · Answer 4

my share snapshot folder was emptied out after the first reinitialization ( 1 month ago)

Sue_H SSC Guru Points: 90860 More actions · Answer 5

DBA-640728 - Tuesday, May 29, 2018 3:25 PM
my share snapshot folder was emptied out after the first reinitialization ( 1 month ago)

I believe it's looking to see if you have ever generated one. Just use the force_invalidate_snapshot. Or generate a new snapshot for the publication and use that.

Sue