Transactional replication keeps applying snapshot

Question

Transactional replication keeps applying snapshot

Tom Schuettke

SSCarpal Tunnel

Points: 4127
More actions
April 19, 2017 at 9:13 am

#401966

Hi,
I created a new snapshot and applied it. It ran the scripts to truncate all the tables and drop the keys. Then it loaded all of the tables and ran the post scripts. I got the "snapshot has been applied" message. At this point it should have gone into transactional mode.
Instead, it immediately started over and truncated all of the tables again and started loading.
I'm afraid it's just going to do it again when this load finishes. (It takes about 7 hours)
Has anyone ever seen anything like this happen?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Only if the publication was setup for snapshot replication. That's really the only time that happens.
Maybe something got confused with the setup and the need for the original snapshot? You don't switch between snapshot and transactional. The first snapshot for a transactional publication would just be used to initialize the publication - but it's still transactional not snapshot replication.
In Replication Monitor, select the publisher and on the right you should have the Subscription Watch list - or a tab to select that. The left drop down you can select the subscription type and see the status and publication. You may want to check what you have there.
At the publisher in the published database, you can execute:
EXEC sp_helppublication;
The description generally will tell you the publication and the publication type as well as some other information about the publication.

Sue

Tom Schuettke SSCarpal Tunnel Points: 4127 More actions · Answer 2

It is standard transactional replication. It's been running fine for about 9 months. I added a table yesterday and created a new snapshot. It reinitialized the publication with the snapshot and then immediately did it again.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Tom Schuettke - Wednesday, April 19, 2017 10:43 AM
It is standard transactional replication. It's been running fine for about 9 months. I added a table yesterday and created a new snapshot. It reinitialized the publication with the snapshot and then immediately did it again.

And what is in the Subscription Watch List? Check both Transactional and Snapshot in the left dropdown.
And did you run sp_helppublication? Did you check the replication frequency value?
You can also execute sp_helpsubscription. You can verify, check statuses all of the values of those two procedures here:
sp_helppublication (Transact-SQL)
sp_helpsubscription (Transact-SQL)

Did you check the properties for the Snapshot Agent and the Log Reader Agent? Those could be out of sync somehow - most likely with however the changes were make.
Not sure how you added the table and reinitialized the snapshot but it seems that something in that process caused issues.

Sue

Tom Schuettke SSCarpal Tunnel Points: 4127 More actions · Answer 4

Subscription watch list shows nothing for snapshot, and my subscription shows under transactional.
sp_helppublication shows 0 for replication frequency.
I see nothing out of the ordinary from sp_helpsubscription.

We've tried to recreate the same steps on our dev server and it works as expected. Added the table, created the snapshot, only the new table was initialized, and transactional replication resumed.

Last night in production it reinitialized the entire database and then did it again. Really strange behavior.

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Check the jobs I mentioned. If the snapshot has completed it would (or should) be disabled and it should list as Completed in Replication Monitor. And the Log reader should be enabled and then running based on however you set up the transactional replication to run.
You could try messing with the jobs at this point and get those set right. If it's working correctly in Dev, you at least have that reference point on what the properties should be.
You may want to consider reinitializing the subscription if you can afford to do that (based on size, time, data needs, etc) with a new snapshot. That could possibly clear things up.

Sue

Tom Schuettke SSCarpal Tunnel Points: 4127 More actions · Answer 6

It's currently reinitializing from the snapshot. Log reader is enabled and running. Everything looks correct and we've matched all the settings to dev.

I'm going to let this run through (it's about half way done). If it does it again I'll open a ticket with Microsoft.

Thanks for your help, I really appreciate you taking some time to look at my problem.

Tom

Sue_H SSC Guru Points: 90860 More actions · Answer 7

You are very welcome. Please post back after the reinit if you can - I'm curious to see if that corrects things or not. Weird issue.

Sue

Tom Schuettke SSCarpal Tunnel Points: 4127 More actions · Answer 8

As soon as the re-init finished it started over again.

So I opened a ticket with Microsoft. The engineer had never seen replication behave like this before.
We removed the table that I had added, and then added it back in.
We created a new snapshot and it did exactly what it was supposed to do, it created a snapshot for just the new table, not the entire database. Then we marked for re-init and created a new snapshot for the subscription and let it load (7 hours). When it finished it started moving transactions like it should.

We never found a cause.

Sue_H SSC Guru Points: 90860 More actions · Answer 9

Thanks for the follow up - much appreciated. I totally hate those where a cause is never found. It's always there somewhere. And I'm foolish enough to pick at it for hours trying to find it. Glad it's all good now.

Sue

JP10 SSCarpal Tunnel Points: 4372 More actions · Answer 10

I tried everything you pointed out still repeats. I tried creating a replication with no indexs and that seemed to work. I had to move onto something else so for now data is being replicated fine and if somebody needs the indexs I will replicate them on a table by table bases.