Danny,

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.

Danny

-- 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 data
sp_MSenum_logreader @name = '%',@show_distdb =0
GO
-- check on the comments, delivered_transactions, delivered_commands, delivery_rate and
-- delivery_latency columns over time to see how the distribution agent is processing data
sp_MSenum_distribution @name = '%',@show_distdb =0
GO

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) ON
sub.publisher_db = art.publisher_db
AND sub.publisher_id = art.publisher_id
AND sub.publication_id = art.publication_id
AND sub.article_id = art.article_id

PRINT '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_id
PRINT '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 s
ON 
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_id
GROUP BY t.article_id,s.agent_id,s.article
ORDER BY s.article
--ORDER BY UndelivCmdsInDistDB DESC --t.article_id

/*
DROP TABLE #MSrepl_commands
DROP TABLE #MSSubscriptions
DROP TABLE #MSDistHistory
*/