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

by

Troubleshooting Guide: Error 3997 – MARS Batch Transaction Still Active at End of Batch

Why This Error Happens

This error occurs when Multiple Active Result Sets (MARS) is enabled on a connection, and an application explicitly begins a transaction (via BEGIN TRANSACTION) inside one batch but fails to commit or roll it back before that batch completes execution. SQL Server 2025 enforces strict transactional boundaries within MARS sessions, and leaving a transaction open across batch boundaries violates connection state integrity. As a safeguard against orphaned locks and inconsistent session state, SQL Server automatically rolls back the incomplete transaction and raises Error 3997.

Step-by-Step Resolution Guide

Step 1: Audit the Application’s Transaction Logic

  • Review the client code (ADO.NET, ODBC, or JDBC) for any BEGIN TRAN statements that are not matched with a corresponding COMMIT TRAN or ROLLBACK TRAN within the same batch execution.
  • Pay close attention to conditional branches (IF/ELSE) where an error path might skip the commit/rollback logic.
  • Search stored procedures for early RETURN statements that bypass transaction closure.

Step 2: Verify MARS Connection String Settings

  • Open the application’s configuration file (e.g., web.config, app.config, or connection string source).
  • Confirm MultipleActiveResultSets=True; is actually required for the workload; if not, disabling it forces sequential batch execution and prevents this specific error class.
  • If MARS is required, ensure the driver version supports SQL Server 2025 transactional semantics (update to the latest Microsoft.Data.SqlClient via NuGet or the ODBC Driver 18+ from the Microsoft Download Center).

Step 3: Implement Explicit TRY/CATCH with Transaction Handling

  • Wrap all transaction logic in T-SQL using BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH.
  • Inside the CATCH block, add logic to check XACT_STATE():
  • If XACT_STATE() = -1, force a ROLLBACK TRANSACTION.
  • If XACT_STATE() = 1, issue a COMMIT or ROLLBACK as appropriate before exiting the batch.

Step 4: Enable Session-Level Tracing for Validation

  • Use SQL Server Profiler or Extended Events (XEvents) on the Windows Server 2025 instance to capture error_reported events filtered by Error Number 3997.
  • Correlate the captured batch_text with the specific client session to pinpoint which stored procedure or ad-hoc query is leaving the transaction open.

Alternative or Verification Steps

  • Verify the Fix: Run the application under load and query sys.dm_tran_active_transactions joined with sys.dm_tran_session_transactions to confirm no transactions remain in an uncommitted state after batch execution.
  • Alternative Solution: If refactoring the application code is not immediately feasible, set SET XACT_ABORT ON at the top of the stored procedure; this forces SQL Server to automatically roll back and terminate the batch immediately upon encountering a runtime error, preventing the transaction from persisting into an invalid state.