Symptoms
Assume that you create an Availability Group by using the WITH DTC_SUPPORT = PER_DB clause in Microsoft SQL Server. When you try to execute a SAVE TRANSACTION statement in a transaction or system stored procedures such as sp_executesql, you receive one of the following error messages:
Msg 627, Level 16, State 0, Line LineNumber
Cannot use SAVE TRANSACTION within a distributed transaction.
Msg 12324, Level 16, State 100, Line LineNumber
Distributed transactions (DTC) are not supported with memory optimized tables.
Error: 3933, Severity: 16, State: 1.
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
Cause
When a database in Always On Availability Group is enabled for WITH DTC_SUPPORT = PER_DB, as long as a transaction involves multiple databases, the transaction is promoted to MSDTC transaction in SQL Server 2016 SP2 and SQL Server 2017. There are limitations in MSDTC transactions, for example save points can not be used, memory optimized tables can not be used.
In some situations cross database transactions happen not because users have transactions involving two user databases, but because the transactions involve the current database and system databases (master, tempdb, model, system resource database). Because system resource database is actually read only, it is not necessary to promote to MSDTC transaction if a cross database transaction involves system resource database but not other system databases.
Resolution
The fix introduced in this KB skips the promotion to MSDTC transaction if system resource database and a user database that is part of an Availability Group enabled for PER_DB DTC are involved in a cross database transaction within the same SQL Server instance.
Therefore if the original cause of errors listed above is cross database transaction involving system resource database, this fix will address it. Sometimes user may see the errors listed above with the same underlying cause. The following lists the conditions under which this fix applies:
-
A database is part of an Always On Availability Group enabled WITH DTC_SUPPORT = PER_DB.
-
A cross database transaction involves this database and the system resource database in the same SQL Server instance.
-
Inside the cross database transaction, SAVE TRANSACTION is used, or memory optimized tables are involved.
Such transaction would fail with errors listed above without this fix. With this fix, such transaction should proceed.
Note: This fix addresses cross-database transactions that involve the system resource database, but any cross-database transaction that involves user databases and contains a SAVE TRANSACTION statement will still fail and must be rewritten.
This issue is fixed in the following cumulative update for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
More Information
Additional information about DTC_SUPPORT = PER_DB, memory optimized tables, SAVE TRANSACTION can be found at the following links.
Database transactions within the same SQL Server instance link this part to https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/transactions-always-on-availability-and-database-mirroring?view=sql-server-2017#sql-server-2016-sp1-and-before-support-for-cross-database-transactions-within-the-same-sql-server-instance
Memory-Optimized Tables link this part to https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-2017#limitations
SAVE TRANSACTION link this part to https://learn.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-2017#remarks
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
References
Learn about the terminology Microsoft uses to describe software updates.
