FIX: Errors 627 or 12324 occur when cross database transactions are promoted to distributed transactions in SQL Server Availability Group

Applies to: SQL Server 2016 DeveloperSQL Server 2016 EnterpriseSQL Server 2016 Enterprise Core More

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:

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:

Cumulative Update 7 for SQL Server 2017       

Cumulative Update 1 for SQL Server 2016 SP2

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.