Sample Header Ad - 728x90

Transactional Replication - Adding a Log Reader Agent Error

0 votes
1 answer
283 views
I am trying to setup Transactional Replication to from Server A (Publisher) to Server B (Subscriber) using a remote Distribution Server - Dist A (Distribution). Dist A already services multiple publications from multiple publishers. ***Details about environment:*** **Server A:** - Version: SQL 2017 v 14.0.3465.1 - Cluster: Traditional FCI - No Availability Groups - Server A is a new installation with 1 empty database that has never been setup for replication before. **Dist A:** - Version: SQL 2022 v 16.0.4085.2 - Cluster: Traditional FCI - No Availability Groups - Dist A is an existing Distribution Server used for Production Transactional and Merge Replication by 4 Publisher Servers and 20 Subscriber Servers with ~30 Unique Publications. I am trying to create my first publication on Server A using a brand new database. I successfully configured the Publisher settings on Dist A (allowing Server A to be a Publisher and dictating which Distribution Database it will use). On Server A, I added Dist A as its distributor, then enabled the database for Transactional Replication. I am now attempting to create the Log Reader for the Database on Server A and receiving the following error: USE [Database]; GO EXEC sys.sp_addlogreader_agent @job_login = N'', @job_password = '', @publisher_security_mode = 1; GO > Msg 3933, Level 16, State 1, Procedure > sys.sp_MSrepl_addlogreader_agent, Line 184 [Batch Start Line 16] > Cannot promote the transaction to a distributed transaction because > there is an active save point in this transaction. When researching this error, I am only finding posts referencing Replication and Availability Groups. **We do not have Availability Groups on Server A or Dist A.** We have confirmed that the items in sys.configurations match those of other servers who are actively publishing. Linked Server settings to the Distributor are equal to those working servers as well. I am trying to find out what setting or configuration item was missed.
Asked by SQL Sean (235 rep)
Apr 25, 2024, 04:06 PM
Last activity: May 26, 2025, 07:04 AM