Sql-server – Replication between SQL Server 2005 – 2005

replicationsql serversql-server-2005transactional-replication

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?

Best Answer

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'
Related Question