asked
Replication on SQL 2005.
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?
Any chance you can trace the Replication Monitor with Profiler and post here the command that takes an unreasonable time?
I have discovered that the stored proc (sp_replmonitorhelppublication) is what is taking so long to run. Now I need to find out why.
With the "tuning" template in Profiler you can capture the individual statements and how long they take to complete. That could be a starter.
![alt text][1] [1]: /storage/temp/667-\capture.jpg Here is a screen shot of the procedures that are taking so long. Still not sure what to do to correct the issue but I am still searching. Thank you for all of your help with this.
Looks like checking the replication status is the long running command. I don't know what to suggest anyway.
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?
I have taken a look at the Profiler but I am not certain which item to select to get the information on the Replication Monitor. The publications do not cross over each other with any data. They are separate databases. The distributor and the publication are on the same server.
Set up a profiler trace to capture all activity from your machine. You can use the default template and add the HostName column, then add a filter to match your machine name. Activity from the Replication monitor should be captured with "Replication Monitor" as the application name.
use [master] exec [distribution].sys.sp_replmonitorhelppublication - duration = 87284, Reads = 1781775 exec sys.sp_replmonitorhelppublisher -- duration 74022, Reads = 4010, this is what I have found. It looks like the distribution is issue??
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.
Clean up is running successfully. One more question. I re-ran the trace and I have discovered that something called Audit Logout Audit Logout Replication Monitor XXXX CPU = 400781, Reads = 7794558, Writes = 12, duration = 441300 StartTime = 08:12:22.173 End Time = 2013-02-15 08:19:43.473. As you can see that took ~7minutes to run. In the course of the trace, there are several Audit Logout's but this was the longest running one. Any idea what the Audit Logout is?
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.
14 People are following this question.
