We help IT Professionals succeed at work.

Replication error each time I alter the table on Publisher

venk_r
venk_r asked
on
Medium Priority
364 Views
Last Modified: 2012-05-12
I have setup Transactional replication on SQL 2008 with subscription setting 'Allow initialization from Backup'.Cannot insert into table name when the identity insert is off.After few hours I get the following error
Error Message : The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.
Can you tell me whats going on.
Comment
Watch Question

Commented:
there may be many things associated. If it is a pull subscription, the pull subscription agent might not be created at the subscriber end.
or the server may be unable to communicate with the distributor. Did you also do snapshot for the replication or added with replication support only type?
For your problem here, either you need to drop the subscriber and add it with no sync option if the commands are not that important.
If the commands are important and both the publisher and subscriber should be synchronized, then you need to recreate the subscription.

Author

Commented:
Thanks for the reply.My problem is my subscription is huge around 1 terabyte.Cannot afford downtime.Is there anyway to getaround it with replication scripts?

Commented:
with such big data, I think log-shipping would be better option.
Anyway, you can remove the subscritpion, don't delete the objects at subscriber end, and add the subsciber with nosync option. Along with that, do tablediff and syncronize the missing data.

Did you try the things suggested before? what happend after changing the timeout and other parameter for the agent profile?

Commented:
please forget the comment part

Did you try the things suggested before? what happend after changing the timeout and other parameter for the agent profile?

Author

Commented:
No Luck yet.I think I need to recreate the whole subscription from scratch

Commented:
What steps you took ? what methods you tried?

Author

Commented:
thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.