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

by

Troubleshooting Guide: Error 3905 — Nested Transaction Limit Exceeded in SQL Server 2025

Why This Error Happens

Error 3905 occurs when a session’s @@TRANCOUNT value reaches its upper boundary because BEGIN TRANSACTION statements are being issued repeatedly without corresponding COMMIT or ROLLBACK calls. This is almost always caused by application-level connection pooling reusing a session that never properly closed an open transaction, or by poorly structured loops/recursive stored procedures that increment the transaction count on every iteration. In rare cases, ORM frameworks (like Entity Framework or Hibernate) mismanaging transaction scopes can silently stack nested BEGIN TRAN calls until the counter overflows.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Session and Query

  • Run DBCC OPENTRAN in SQL Server Management Studio (SSMS) to identify long-running or orphaned transactions.
  • Query sys.dm_tran_active_transactions joined with sys.dm_tran_session_transactions to find the exact session_id with an abnormally high @@TRANCOUNT.
  • Cross-reference the session_id with sys.dm_exec_requests to capture the sql_handle and extract the exact query text causing the issue.

Step 2: Audit and Correct Application/Procedure Code

  • Open the stored procedure or application source code (e.g., C#, Java) identified in Step 1.
  • Ensure every BEGIN TRANSACTION has a matching COMMIT TRANSACTION or ROLLBACK TRANSACTION, specifically checking TRY...CATCH blocks where a ROLLBACK might be missing after an exception.
  • If using nested procedures, replace manual BEGIN TRAN calls with a check: IF @@TRANCOUNT = 0 BEGIN TRAN.
  • For connection pooling issues on Windows Server 2025, verify the application’s connection string includes Enlist=false if distributed transactions are not explicitly required.

Step 3: Terminate the Stuck Session and Reset the Pool

  • If the transaction count is already maxed out and blocking production traffic, execute KILL [session_id] in SSMS to forcibly terminate the connection and roll back the stack.
  • Restart the SQL Server (MSSQLSERVER) service via the Services console (services.msc) on Windows Server 2025 if KILL does not release the session within 30 seconds.
  • Clear the application-side connection pool (e.g., restarting the IIS Application Pool on Windows 11/Server 2025) to ensure a fresh, zero-trancount connection is established on the next request.

Step 4: Implement Trancount Safeguards

  • Add a SET XACT_ABORT ON statement at the beginning of critical stored procedures to ensure automatic rollback on error, preventing the transaction count from climbing due to unhandled exceptions.
  • Deploy a scheduled SQL Agent Job that runs the sys.dm_tran_session_transactions query hourly to alert administrators via Database Mail if any session’s trancount exceeds a safe threshold (e.g., 50).

Alternative or Verification Steps

  • Verification: After deploying the code fix, run SELECT @@TRANCOUNT immediately after executing the specific stored procedure in a test environment; the result should return to 0 after the transaction logic completes.
  • Alternative: If the root cause remains elusive, enable a server-side trace or Extended Events session filtering on the sqlserver.error_reported event where error_number = 3905 to capture the exact T-SQL batch text in real-time for deeper analysis.