2

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?

  • Do you want to create an alert based on the backlog of the replicated commands ? – Kin Shah Feb 26 '14 at 17:14
  • Yes. I do have an update on my original question. The 338694 undelivered commands were for the 'virtual' subscriber_db; I forgot those need to be excluded. But, an alert for when/if regular replication gets behind would be good! – Tammy Feb 26 '14 at 20:06
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'
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.