no replicated transactions are available

  • dyun

    SSC-Addicted

    Points: 434

    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

    Hall of Fame

    Points: 3738

    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

    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

    SSChasing Mays

    Points: 654

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

    [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

    SSChasing Mays

    Points: 654

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

    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

    SSChasing Mays

    Points: 654

    Winash,

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

    Thanks for your help.

  • mpalaparthi

    Mr or Mrs. 500

    Points: 578

    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

    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

    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!

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

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