Replication between SQL Server 2005 - 2005

Question

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.

Tammy

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?

Answer 1

I have found a solution to my problem. Beware though, the steps outlined below broke regular replication (or so it seems). Nevertheless, I was able to get replication running, and now there are no undelivered commands building up in the distribution database:

I Command You To Deliver Those Commands

The solution provided there is to run:

exec sp_changepublication
    @publication = 'xxxxx',
    @property = N'allow_anonymous', 
    @value = 'false'
exec sp_changepublication
    @publication = 'xxxxx',
    @property = N'immediate_sync',
    @value = 'false'

Answer 2

Try looking into the SQL shipped view in the distribution database, MSdistribution_status. It lists the agent_id, article_id, as well as UndelivCmdsInDistDB (The number of commands pending delivery to Subscribers.). You should be able to join against msDistribution_agents and msArticles to match the pending commands to the exact table/subscription. You can probably set up a job to monitor the UndelivCmdsInDistDB and create an alert if it goes above a threshold.

Replication Tables http://technet.microsoft.com/en-us/library/ms179855.aspx