I'm hoping to get some help with a SQL Server 2005 replication issue. In short, in replication monitor, (On the Publisher), on the 'Distributor to Subscriber History' tab, I see two lines under 'Sessions of the Distribution Agent'. One is running fine, while the 2nd one is showing the action message 'The process is running and is waiting for a response from the server'. I now have 338694 undelivered commands 'supposably' according to this query:
Select sum(UndelivCmdsInDistDB) FROM [distribution].[dbo].[MSdistribution_status];
But - I just executed counts on one of the replicated tables on both servers, and the are spot on. Therefore, I do believe the agent that is hung simply needs to be killed.
P.S. Replication stopped at 10pm last night ; I believe that when I paused and restarted the SQL Server agent this morning on the Subscriber, the 2nd distribution agent session started and picked up with delivering the commands to the subscriber.
P.S.S - I have also been working to try to create some sort of alert for this situation, any thoughts on that?