I'm pretty new to Replication, so please excuse the newbie sort of approach. We have a development database ICC4, and there is a publication set up to copy tables from ICC4 to the CentralData DB. It is a transactional publication, and CentralData exists on multiple servers.
We have a nightly overlay, where ICC4 is copied from production over to the development server. From what I read, we were supposed to basically delete the subscriptions and publication, overlay the database and recreate the publication and subscriptions afterward.
I used the Generate Scripts method to delete before, and create afterward, but ran into trouble. There still appear to be orphaned publisher records and the code below kept failing (so I commented it out). This made sense since the qreader was never deleted in the earlier code.
--exec [EPC].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
We've disabled the publication while we try to figure this out, but I see references to ICC4 in multiple distribution tables. So my questions are:
Is there a better/cleaner way to stop or disable replication while a database is overlayed and then continue it up afterward?
How do I remove the orphaned records that our monitoring system keeps telling us about, with the message: "Replication-Replication Transaction-Log Reader Subsystem: agent _____-ICC4-7 failed. The process could not execute 'sp_repldone/sp_replcounters' on '________'."
I appreciate any insight you guys can provide. Thanks.