SQL 2005 Pull Subscriber stuck "Uploading data changes to publisher"

I recently ran some schema updates on a SQL 2005 Merge Replication system with 7 pull subscribers. The updates included a new table and a foreign key between an existing replicated article and the new table, as well as some additional columns on other tables. Without thinking about the consequences, I ran the schema updates on the publisher first, and immediately saw errors because the foreign key couldn't be replicated (because the referenced table didn't exist at the other servers.) I then added the new table as an article and generated a new snapshot, thinking replication would push the new article, replicate the remaining schema changes, and all would be fixed. However, replication was still stuck trying to replicate the schema changes. So, I created the new table at all 7 subscribers, which allowed the other schema changes to finish, but then couldn't properly add the new article at the subscribers because it couldn't drop and recreate a table referenced by another table. At this point, I decided to recreate all the subcribers. No data changes were being made at the time, so I deleted each subscriber, applied the schema changes manually to each server, recreated each subscriber, and chose not to reinitialize the data. I waited for all seven servers to complete the initialization, and everything seemed fixed.

note: In the future, I'll add new articles at the publisher with no constraints, generate a snapshot, let replication synchronize, then add the constraints in, and let replication handle the other schema changes. I've learned my lesson!

Here is the problem: Two of the seven subscribers are not replicating! Changes at the publisher are not downloaded, and changes at the subscriber are not uploaded to the publisher. The replication monitor shows no errors, and the agent appears to be stuck on "Uploading data changes to the publisher." I enabled verbose logging and saw nothing out of the ordinary. The last message in the log file is the same message, "Uploading data changes to the publisher."

Please help me! I'd happy to provide more details of the configuration. Also, the clients took the servers offline to apply SQL Service Packs, which I haven't looked into yet. 2005 Replication has been fairly reliable for me, with server shutdowns or service interruptions not ever comprimising the data, and honestly, I'm hoping that isn't the issue.

Thank you!

Drew Detwiler

I haven't run the profiler yet, but all dependet objects are included in the publication.

While doing some more testing, I realized that the two suspect subscribers have another symptom in common. A simple update query on a simple table with 10 rows took over a minute on both databases. I looked in the activity monitor and couldn't find any locks on the object. As soon as I stopped the merge agent at the subscriber, the query ran instantly! I started the agent back up, and again, the query took over a minute. Is my best option to just drop and recreate the subscriptions This is possible now, since we aren't in production yet and the amount of data being reintialized is small enough to replicate within a few hours.

Should I drop and recreate the publisher and all subscribers I feel like it is best to start on a clean slate while I still can.

Drew