SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


no replicated transactions are available


no replicated transactions are available

Author
Message
dyun
dyun
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 266
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.
dbaforever
dbaforever
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 539

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
dyun
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 266

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
vellenks
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 247
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
winash
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3030 Visits: 1883
[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
vellenks
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 247
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
winash
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3030 Visits: 1883
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
vellenks
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 247
Winash,
i have posted a reply on the other thread; let's continue there.
Thanks for your help.
mpalaparthi
mpalaparthi
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 566
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
Hemant Korde
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 132
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search