none
Setting up transactional replication fails with error 3933 on sp_addlogreader_agent when using a remote distributor

    Question

  • Hi,

    I've got the following set up:

    • One server cluster (active/passive) Windows Server 2012 with SQL Server 2014 (12.0.5589.7) as publisher
    • One server running Windows Server 2012 with SQL Server 2014 (12.0.5589.7) as distributor
    • One server cluster (active/passive) Windows Server 2012 with SQL Server 2014 (12.0.5589.7) as subscriber

    The "publisher" has an alias pointing to itself on non-standard port and appears to be Linked to itself when looking at LinkedServers.

    I've tried setting up the source SQL Server as both distributor and publisher, in which case everything runs smoothly.
    However, when I try to set it up the way we want it (using another server as distributor, still inside the same network)
    I run into problems at this point:

    exec [BAMPrimaryImport].sys.sp_addlogreader_agent
    @job_login = N'nnn\SVC-SqlReplUsr',
    @job_password = 'mypassword',
    @publisher_security_mode = 0,
    @publisher_login = N'repl_admin',
    @publisher_password = N'mypassword',
    @job_name = null

    The error I get is:

    Msg 3933, Level 16, State 1, Procedure sp_MSrepl_addlogreader_agent, Line 184 [Batch Start Line 7] Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.

    I've tried running dbcc opentran, but there are no open transactions.
    I've also tried:

    EXEC sp_configure 'remote proc trans', '0'; RECONFIGURE;

    To no avail. 

    I've tried Googling this but now I start to realize that this is well above my knowledge. Does anyone have any ideas what to try next?

    Thursday, October 4, 2018 11:49 AM

All replies

  • In this session, can you do this rollback tran. Do this repeatedly until you get the message: 

    The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

    Then try to run the sp_addlogreader_agent proc again - try this:

    exec [BAMPrimaryImport].sys.sp_addlogreader_agent
    @publisher_security_mode = 0,
    @publisher_login = N'repl_admin',
    @publisher_password = N'mypassword'

    Thursday, October 4, 2018 12:32 PM
    Moderator
  • Hi, I tried this, there was no transaction (got the message on the first try) and then I ran the call as you specified, but I got the same error. I've tried looking for any active save points all over, but haven't found anything. 
    Thursday, October 4, 2018 1:49 PM
  • Hi,

    I ran into the same error and this link helps me.

    https://am2.co/2018/02/replication-availability-groups-2/

    Sunday, October 14, 2018 7:00 AM