Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

no replicated transactions are available Expand / Collapse
Author
Message
Posted Monday, August 22, 2005 1:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:44 AM
Points: 8, Visits: 246
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.
Post #212720
Posted Tuesday, August 23, 2005 12:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:41 PM
Points: 150, Visits: 499

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

 

 

 

Post #213077
Posted Friday, August 26, 2005 11:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:44 AM
Points: 8, Visits: 246

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

Post #214518
Posted Thursday, September 23, 2010 7:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:27 AM
Points: 34, Visits: 245
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
Post #992001
Posted Thursday, September 23, 2010 8:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
[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?



Post #992063
Posted Thursday, September 23, 2010 8:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:27 AM
Points: 34, Visits: 245
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
Post #992090
Posted Thursday, September 23, 2010 11:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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
*/





Post #992216
Posted Thursday, September 23, 2010 12:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:27 AM
Points: 34, Visits: 245
Winash,
i have posted a reply on the other thread; let's continue there.
Thanks for your help.
Post #992261
Posted Wednesday, June 15, 2011 9:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:03 PM
Points: 42, Visits: 525
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
Post #1125855
Posted Friday, December 14, 2012 4:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 12:05 PM
Points: 6, 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
Post #1396581
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse