sql-serversql-server-2005replication

SQL Server Subscription Initialization Restarts Endlessly, Never Finishes


I'm trying to set up transactional pull replication on 2 SQL Server 2005 instances, through a 3rd as a distributor. When the subscription is being initialized, it bulk inserts properly, giving the message that the snapshot was successfully loaded. Then it makes primary key indexes as usual.

At this point the job starts over, dropping all the tables and bulk inserting again. It loops endlessly and never finishes, until the snapshot expires and a new one has to be made. I need help diagnosing this problem, as I have checked all the error logs I know of, and didn't see anything that might be of relevance.


Solution

  • Check to see if there are any tables with corrupted primary keys in the publication. I have seen instances where that causes SQL Server transactional replication to behave in bizarre ways.