Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to manage database replication in SQL Server 2005. I have set up my publications, distributors and subscriptions and it seems to work properly at the start.

However, after a period of time, my subscription becomes deactivated, and requires reinitialization.

This is transactional publication, with a non-updatable, pull subscription. The publication has the "Subscription Expiration" option "Subscriptions never expire, but they can be deactivated until they are reinitialized" selected. The publishers and subscribers are on the same LAN.

What are the common reasons for a subscription to be deactivated?

For instance, could an insert failure on the subscriber like the following (due to badly handled schema change) cause a subscription to become deactivated?

Cannot insert explicit value for identity column in table 'SOME_TABLE' when  
IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)

If this can cause a subscription to be deactivated, what are some other scenarios to look out for?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

Yes that would be the cause. When the insert fails, the replication will then fail. Once the replication is in a failed state you've got to fix the problem within a few days before the subscription will expire and you'll need to reinit it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.