no replicated transactions are available

Question

Post reply

no replicated transactions are available

dyun

SSC-Addicted

Points: 434
More actions
August 22, 2005 at 1:23 pm

#152519

I am replicating data to two different subscripton databases from the same publisher DB. One subscription works fine while the other shows 'No replicated transactions are available' with no error messages. The subscription that has the issue is also the distribution. Any feedbacks will be highly appreciated. Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

dbaforever Hall of Fame Points: 3740 More actions · Answer 1

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

dyun SSC-Addicted Points: 434 More actions · Answer 2

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

vellenks Right there with Babe Points: 764 More actions · Answer 3

Hi Danny,

Had you solve this problem. I am getting the same with "No replicated transactions are available" while i know there is a very big process running at the publisher.

Thanks.

Vellen

winash SSChampion Points: 11446 More actions · Answer 4

[edit]: Realized after just posting this that this is the SQL 7,2000 forum - the queries below run in my SQL 2008 environemnt and I don't know if they will work in 7 or 2000.

What kind of replication do you have setup? Transactional or snapshot or merge or P2P?

Assuming this is transactional replication:

Are you seeing the "No replicated transactions are available" for the log reader agent or the distribution agent?

Run the following queries in your distribution server in the context of the distribution DB to check on what is happening in the log reader and distributor agents:

-- 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

Are there any errors in replication monitor or in the distributor/log reader jobs?

vellenks Right there with Babe Points: 764 More actions · Answer 5

It is a Transactional replication.

The no replicated commands is at the Distributor only.

I can see the log reader sending commands frequently to the Distributor. The distributor is growing fast.

I made a backup earlier of the distributor to truncate the log at the publisher.

The log_reader keeps blocking the cleaning jobs and my replication monitor screen as well.

There is a big job running at the publisher with a 50GB of transact log.

Below is extract of the results:

Log Reader

comments:2 transaction(s) with 2 command(s) were delivered.

delivery_time:8655091

delivered_transactions:3332865

delivered_commands:10141021

average_commands:3

delivery_rate: 1171

delivery_latency: 0

Distributor agent

comments:No replicated transactions are available.

delivery_time:0

delivered_transactions:0

delivered_commands:0

average_commands:0

delivery_rate:0

delivery_latency:0

error_id:0

job_id:0x59BFF67CF73193449AD40A4D58556FAB

local_job:1

profile_id:4

agent_id:1

last_timestamp: 0x00000000002A6D19

winash SSChampion Points: 11446 More actions · Answer 6

So - there's no errors in replication monitor or in the distribution agent job history but the data doesn't seem to be flowing into the subscriber.

What happens when you open replication monitory, double click on your subscription and click on the "Undistributed Commands" tab? This tab will take some time to populate if there is a lot of data getting replicated - do you see any values here?

What is the result of this on the distribution DB:

SELECT SUM(rows) FROM sys.partitions WHERE object_id = OBJECT_ID('msrepl_commands');

This will provide the # of rows that have come into the distribution DB from the publisher.

This link has some good info on how to troubleshoot distribution agent performance (scroll to the section "Distribution Agent reader latency" and "Distribution agent writer latency") - http://blogs.msdn.com/b/repltalk/archive/2010/02/21/transactional-replication-conversations.aspx

The information and scripts provided there might be helpful in figuring out where the data is. The following script (adapted from the MSDistribution_status view) might be helpful - note that it will take a long time depending on the amount of data in the MSrepl_commands and the MSDistribution_History tables:

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

*/

vellenks Right there with Babe Points: 764 More actions · Answer 7

Winash,

i have posted a reply on the other thread; let's continue there.

Thanks for your help.

mpalaparthi Say Hey Kid Points: 688 More actions · Answer 8

Hello,

I am getting the same error "No replicated transactions are available". I am getting in the Log reader agent. When I run the command SELECT SUM(rows) FROM sys.partitions WHERE object_id = OBJECT_ID('msrepl_commands'); I got 5915 rows.

The snapshot agent is running fine and is showing as 100% A snapshot for 45 articles was generated

My publisher and distributer are on same machine and subscriber is on different machine. I am using Pull subscription.

Please help me, I have to resolve the issue urgently.

Thanks a Lot

Hemant Korde Mr or Mrs. 500 Points: 532 More actions · Answer 9

Hi,

Does anyone have an answer to above post? I am in the same situation and unable to find out why snapshots, though getting generated properly, are not being applied to the subscriber.

msrepl_commands table is showing a count of over 1 million. Initially when this snapshot was created it worked for few days and then stopped working for some reason. There are no system related changes. I am doing Push subscription and have Publisher and Distributor on the same server.

Thanks

SafeDistance Valued Member Points: 50 More actions · Answer 10

Guys, I know this is an old thread... but people find it again all the time so...

I had the 'no replicated transactions issue' in a Transactional Replication... no errors were displayed anywhere... all agents were running successfully but no data was being delivered.

For some reason, I decided to just restart the log reader. On the first attempt I received the error "The process could not execute 'sp_repldone/sp_replcounters on the publishing server."

So, in response to that I ran sp_replrestart in the Publication database.

Then I restarted the log reader once again.

It started delivering the snapshot right away.

I hope this helps someone... good luck... keep trying!