I have had 1 publication for about 2 years with 76 subscribers and have had no issues. I recently added a second publication that points to a different database with 1 subscriber. Ever since doing that, when I lauch the replication monitor on the original publication, it takes about 3 minutes to display the publications where it used to take a few seconds. I can click on the Subsciption Watch List tab and that list populates almost immediately. I am concerned that something is not right and it may haunt me down the road. Any ideas?
Have you run a PerfMon and Profiler trace while bringing up the monitor yet? Do the publications cross over each other with any data? Where is your distributor? On the publication server or a different server?
The replication monitor relies a lot on the distribution database. I am not sure what commands are sent to populate the monitor (you found some through profiler already) so that would be where to start looking. Generally, the pending and distributed commands are stored for a certain amount of time before being marked for deletion. However, it is possible that the purge job is not running and so the commands are not being removed. Take a look in the SQL Server Agent to see if the job "Distribution cleanup: [distribution database name]" is there and running correctly.
Well I got fed up because it was taking even longer to launch the replication monitor. I decided to delete the new publisher that I had created, and guess what? The replication pops open like it always had in the past. Now I was doing some research to see what might be going on. I found a script that gives all sorts of detail about replication. When I ran the script after the deletion, it returns nothing. The problem is the MSArticles table now contains no records. Oddly enough replication is still working for the existing publisher. Does any of this make sense? Any Ideas? The script looks like this: USE Distribution GO SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Get the publication name based on article SELECT DISTINCT srv.srvname publication_server , a.publisher_db , p.publication publication_name , a.article , a.destination_object , ss.srvname subscription_server , s.subscriber_db , da.name AS distribution_agent_job_name FROM MSArticles a JOIN MSpublications p ON a.publication_id = p.publication_id JOIN MSsubscriptions s ON p.publication_id = s.publication_id JOIN master..sysservers ss ON s.subscriber_id = ss.srvid JOIN master..sysservers srv ON srv.srvid = p.publisher_id JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id AND da.subscriber_id = s.subscriber_id ORDER BY 1,2,3
I did and it is empty. Completely empty. I am thinking about restoring the distribution database from a backup but I am fearful this may cause the replication to fail completely. I am not sure what I am going to do next. I am still researching.
I recreated the same type of scenario (two different publications on two different databases running on the same server) on our development system. I noticed only the transactional publication articles show up in msarticles tables. So maybe this is not an issue after all. The same behavior occured in production as well. I guess I am back to square one trying to figure out why it runs sooo slow with two publications.