In this blog post, let’s learn about the error message “3933 – Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
3933 – Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
Reason for the Error
The SQL Server Error Msg 3933 occurs when a transaction cannot be promoted to a distributed transaction because there is an active save point. This error message can happen when using distributed transactions within a SQL Server database and is typically encountered when attempting to commit or roll back a transaction with an active save point.
Here is an example of a situation that can cause the error:
BEGIN TRANSACTION
SAVE TRANSACTION my_savepoint
BEGIN DISTRIBUTED TRANSACTION
-- perform some operations on a linked server
COMMIT TRANSACTION
COMMIT TRANSACTION
In this example, a transaction is started, and a save point is created. Then, a distributed transaction is initiated using the BEGIN DISTRIBUTED TRANSACTION statement to perform some operations on a linked server. However, when attempting to commit the transaction using COMMIT TRANSACTION, the error message “Msg 3933, Level 16, State 1, Line 5 Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.” will be generated.
Solution
To resolve this issue, you can either remove the save point using the ROLLBACK TRANSACTION my_savepoint statement before initiating the distributed transaction or start a new transaction for the distributed transaction using the BEGIN TRANSACTION statement.
