How to fix Error 3991 on Windows Server 2025 / Windows 11

by

Why This Error Happens

Error 3991 occurs when a COMMIT or ROLLBACK statement is executed inside a stored procedure, trigger, or user-defined function that terminates the outer transaction context that was already active before the routine was called. SQL Server 2025 strictly enforces transaction nesting rules, and modifying the parent transaction’s state from within a nested routine is not permitted. This is typically caused by poorly designed error-handling logic (e.g., a TRY...CATCH block issuing a blind ROLLBACK without checking @@TRANCOUNT) or legacy code migrated from older SQL Server versions that didn’t enforce this as strictly.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Routine

  • Check the full error message text — it names the specific procedure, trigger, or function (e.g., “…inside of it, which is not allowed” will reference the object name).
  • Open SQL Server Management Studio (SSMS) and script out the object using sp_helptext 'YourProcName' or right-click → Modify.
  • Look specifically for COMMIT TRANSACTION, ROLLBACK TRANSACTION, or COMMIT WORK statements inside TRY...CATCH blocks.

Step 2: Fix the Transaction Logic

  • Wrap rollback logic with a check against XACT_STATE() instead of blindly calling ROLLBACK:
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION;
  • Avoid starting a new BEGIN TRANSACTION inside a routine if one is already active; use SAVE TRANSACTION (savepoints) instead for nested logic.
  • Ensure every BEGIN TRY has a matching CATCH block that does not terminate a transaction it did not start.

Step 3: Validate Transaction Count Before Exit

  • Add a check at the end of the routine to confirm @@TRANCOUNT matches the value captured on entry:
DECLARE @TranCountStart INT = @@TRANCOUNT;
-- routine logic here
IF @@TRANCOUNT <> @TranCountStart
    RAISERROR('Transaction count mismatch detected', 16, 1);
  • Deploy the corrected object using ALTER PROCEDURE or ALTER FUNCTION via SSMS or sqlcmd.

Step 4: Clear Cached Execution Plans

  • After altering the object, run DBCC FREEPROCCACHE (or targeted sp_recompile 'YourProcName') to ensure Windows Server 2025’s SQL Server instance isn’t executing a stale, cached version of the faulty logic.
  • Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER if the issue persists after recompilation.

Alternative or Verification Steps

  • Re-run the original query or application transaction that triggered Error 3991 and confirm it completes without raising the exception; check SQL Server Logs (Management → SQL Server Logs in SSMS) for recurrence.
  • If the error persists, enable Extended Events (xp_trace or the “Errors and Warnings” template) to capture the exact call stack and transaction state at the moment of failure for deeper root-cause analysis.