Issue with Transactional replication

[kodaksmile]

Hi,

We have run into an issue with transactional replication. Due to having to do an incremental conversion for some software (converting from Pervasive to SQL 2005 running on x64) we have had to partially remove replication (from the publisher). We use a pull subscription on a db that is roughly 300 gig. The distributor db is left in place as is the actual subscriber which has some views installed that do not exist in the publisher as the subscriber is used for reporting. We successfully generated a new snapshot and after about 9 hours of the data being bulk copied and most of the indexes being built there was a message that the replication agent had not logged any activity for 10 minutes. Next thing we see is a new log in replicaton monitor showing that it is initializing (meaning it is running all the scripts.pre which wipe all the data that has been loaded out of the tables and start all over. The only thing I saw was 3 users did connect to the db to run reports however would that cause replication to reinitialize? (no they weren't supposed to connect but..) Any thoughts or suggestions on what causes this and how to prevent it? We were within an hour of having replication complete and nw need to start over with the load from the snapshot......

thanks!

 

[Broad21]

I believe someone accidentally click "Initialization" or the SQL Agent service was restarted.

It happened to me before as well, Distribution agent only work when you ask it for, it wont automatically choose to reinitialize subscription.

Fix, control security and make sure only few people have control over replication.

 

[kodaksmile]

Not sure this is the case for us as the job was running and all that was done was to kill the users who had logged in to run reports. The agent does not show it was restarted.


Any other thoughts on what could cause this?

 

[kodaksmile]

Found something else that seems odd - it is building the primary keys and then in the middle there is a message regarding the snapshot being delivered then more it starts building the primary keys from the beginning all over.

 

[Broad21]

Hmmm....it seems your replication is internally mess-up. Do you have the luxury to rebuild everything?

If I were you I would uninstall everything, including distributor and start everything all over.

How long you have been sucked in the condition like this?

Consulting with MS support would be another alternative.

 

[kodaksmile]

Replication itself has been running fine for quite a while however, due to the application/data conversion we have to do incremental conversions while some folks are already live on the new software requiring us to rebuild replication. Since there are a many views and components that are only installed on the reporting server we tried to leave the subscriber in place and just have the replicated tables get overwriten. (which worked fine in the test environment) We had an issue when we did the original setup and had involved MS support then as it was a similar issue where replication began looping without intervention (including creating the snapshot) but, also generated out of memory errors. They made a couple of adjustments to the default replication settings and had us reboot the server and run from the snapshot forward and it worked. They were unable to tell us exactly what the issue was :-(