I had this situation recently, using Snapshot replication in SQl Server 2000.
After much head-scratching, the problem turned out to be NOT ENOUGH DISK SPACE on the server that was the publisher.
The Snapshot Agent would run and complete successfully.
But the Distribution Agent would fail with the same message you are getting. In my case, I was trying to distribute to another server - yet for some reason, SQL Server (apparently) did not have quite enough free-space on the disk drive to perform the distribution.
This error condition can be caused by problems other than not enough disk space - but it is worth checking out.
Good luck. John
Thank you John for your valuable information. However, Disk space doesn't seem to be the issue. The publisher is publishing the same articles to two subscriptions. One works just fine while the other has the status of 'Idle' and 'no replicated transactions are available'. If it were the issue of disk space of the publisher server, the replication should fail in both subscriptions. Thanks very much.
-- check on the comments, delivered_transactions, delivered_commands,-- average_commands, delivery_rate and delivery_latency over time to see how the-- log reader agent is processing datasp_MSenum_logreader @name = '%',@show_distdb =0GO-- check on the comments, delivered_transactions, delivered_commands, delivery_rate and-- delivery_latency columns over time to see how the distribution agent is processing datasp_MSenum_distribution @name = '%',@show_distdb =0GO
CREATE TABLE #MSrepl_commands(article_id int NOT NULL,publisher_database_id int NOT NULL,xact_seqno varbinary(16));CREATE TABLE #MSSubscriptions(agent_id int NOT NULL, article_id int NOT NULL, publisher_database_id int NOT NULL,article sysname NOT NULL);CREATE TABLE #MSDistHistory(agent_id int NOT NULL, maxseq varbinary(16) NOT NULL);PRINT CAST(getdate() as varchar(20))INSERT #MSrepl_commands(article_id,publisher_database_id,xact_seqno)SELECT article_id,publisher_database_id,xact_seqno FROM MSrepl_commands WITH (NOLOCK);PRINT 'Inserted into #MSrepl_Commands'PRINT CAST(getdate() as varchar(20))INSERT #MSSubscriptions(agent_id,article_id,publisher_database_id,article)SELECT sub.agent_id,sub.article_id,sub.publisher_database_id,art.article FROM MSsubscriptions sub with (NOLOCK) INNER JOIN MSarticles art WITH (NOLOCK) ONsub.publisher_db = art.publisher_dbAND sub.publisher_id = art.publisher_idAND sub.publication_id = art.publication_idAND sub.article_id = art.article_idPRINT 'Inserted into #MSSubscriptions'PRINT CAST(getdate() as varchar(20))INSERT #MSDistHistory(agent_id,maxseq)SELECT agent_id,'maxseq'= isnull(max(xact_seqno),0x0) FROM MSdistribution_history with (NOLOCK) GROUP BY agent_idPRINT 'Inserted into #MSDistHistory'PRINT CAST(getdate() as varchar(20))CREATE CLUSTERED INDEX CX_MSreplcmds_temp ON #MSrepl_commands(xact_seqno)PRINT 'Created index on #MSrepl_Commands'PRINT CAST(getdate() as varchar(20))SELECT t.article_id,s.agent_id,s.article,'UndelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0 END),'DelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno <= h.maxseq THEN 1 ELSE 0 END)FROM #MSrepl_commands as t INNER JOIN #MSSubscriptions sON t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id INNER JOIN #MSDistHistory as h ON h.agent_id=s.agent_idGROUP BY t.article_id,s.agent_id,s.articleORDER BY s.article--ORDER BY UndelivCmdsInDistDB DESC --t.article_id/*DROP TABLE #MSrepl_commandsDROP TABLE #MSSubscriptionsDROP TABLE #MSDistHistory*/