Replication and Availability Groups with per database DTC support
In SQL Server 2017, when using Availability Groups with Per-Database DTC Support enabled, you are unable to configure replication.
The error received is:
Msg 3933, Level 16, State 1
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
By design, per db DTC support does not allow save points. However, Replication system stored procedures make extensive use of save points. As a result, this creates a conflict where you cannot have a database in an AG with both Replication and per DB DTC support.
As a workaround, you must disable per DB DTC support in order to configure (or change configuration) of Replication.
After much testing on 2016 SP2 CU5 and CU10, I was able to fix this issue. It's related to the following setting. By setting this to '0' I was able to successfully add the publication with no issues.
EXEC sp_configure 'remote proc trans', 0 ;
Same error noted on 2016 SP2 CU5.
2016 SP2 CU10 - this is still an issue. Please fix!
https://support.microsoft.com/en-us/help/4483593/snapshot-isolation-and-savepoint-support-added-for-availability-group - So is this bringing us towards a supported state for the combination of AG+DTC+CDC/CT/REPL?
[Deleted User] commented
This will be addressed in CU14 for SQL Server 2017
Max Enroth commented
This is a major issue for us
2016 SP2 CU5 should fix this
Same issue. Last week I applied SQL 2016 SP2 to both nodes of an AG. I have a database that was once performing trans. repl. and this has been removed. As a result we have 2x CDC SQL jobs (capture & cleanup). The capture job was showing this error message:
Executed as user: domain\user. Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction. [SQLSTATE 42000] (Error 3933)
The other major consequence was the transaction log had grown considerably and could not be shrunk. The affected DB was showing [REPLICATION] in the log_reuse_wait_desc as per this article by Paul Randal:
I removed option 'per database DTC support', was then able to run this command:
EXEC sys.sp_removedbreplication 'DBName';
The capture job is now working as expected & I was able to bring the log file back to normal size.
FYI - I cannot enable the option [per database DTC support] the capture job immediately starts to output the same error as shown above
James Eckhardt commented
Same issue. culminates in stored procs raising the error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION"
...SQL 2016 SP2 running as a multi-subnet fail-over cluster.
Mike Leaman commented
Please fix this. AGs need to fully support replication.
On SQL 2016 SP2 - CU2 the problem still exists, disabling "per database DTC support" fixed the issue, waiting for the fix in next CU patch release.
On SQL 2016, this is an issue, after removing option 'Per Database DTC Support' this issue was resolved. FIX IT !!!!!!!
Same problem on SQL Server 2016 SP2 - CU1
Waiting for the bug fix
Sydney Croud commented
This also affected me when I had CDC on along with HA, as CDC is categorized as replication
jamie hutchings commented
Andy is correct, this needs to be fixed. Ran into this issue yesterday and only way to resolve was disabling per DTC support. Please fix.