none
Transactional Replication in SQL 2014 RRS feed

  • Question

  • Recently we configured Transactional Replicaiton in SQL Server 2014 ENT.

    Both publisher and subscriber are on SQL Server 2014 ENT.

    it was working fine. But now getting msg like ,

    Replicated transactions are waiting for next log backup or for mirroing partner to catch up

    Just verified that log backups are heppening successfully.

    Wednesday, August 21, 2019 5:23 AM

Answers

  • If you want to log ship your distribution and publication databases to a standby server you can ensure that they are in sync.
    • Marked as answer by VijayKSQL Tuesday, September 3, 2019 6:59 PM
    Tuesday, August 27, 2019 8:37 PM
    Moderator

All replies

  • I think Mirroring is in place on that database. Refer this article.

    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    Wednesday, August 21, 2019 11:44 PM
  • Hi VijayKSQL,

     

    Have you apply the latest CU for the sql server 2014? Would you please try to enable trace flag 1448? This trace flag allows the Log Reader Agent to continue replicating changes regardless of the mirroring state. For more details, please refer to https://sqlactions.wordpress.com/2014/10/04/fixreplicated-transactions-are-waiting-for-next-log-backup-or-for-mirroring-partner-to-catch-up/

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, August 22, 2019 6:21 AM
  • Hi Vivek,

    There is no mirroring and any other HA technologies.

    It is only configured with Transactional Replicaiton.

    Thursday, August 22, 2019 5:57 PM
  • Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) - 12.0.6329.1 (X64)
    Thursday, August 22, 2019 5:58 PM
  • You might need to use trace flag 1448.

    What does this return?

    select is_published, is_subscribed, is_merge_published, is_sync_with_backup from sys.databases where DATABASE_ID=DB_ID()

    Monday, August 26, 2019 1:51 PM
    Moderator
  • is_published    is_subscribed   is_merged_published  is_sync_with_backup

    1                     0                    o                               1

    Monday, August 26, 2019 11:00 PM
  • You need to disable sync with backup.

    Before we do that, I take it you are not using mirroring, and the database is not protected by an Availabiltiy Group?

    If this is the case do this

    sp_replicationdboption 'DatabaseName','Sync with Backup',false

    Tuesday, August 27, 2019 3:35 PM
    Moderator
  • Now it is working fine after Disabled.

    But what is the advantage of this feature if we only use Transactional Replication?

    Tuesday, August 27, 2019 8:30 PM
  • If you want to log ship your distribution and publication databases to a standby server you can ensure that they are in sync.
    • Marked as answer by VijayKSQL Tuesday, September 3, 2019 6:59 PM
    Tuesday, August 27, 2019 8:37 PM
    Moderator