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

by

Troubleshooting Guide: Error 3996 — Snapshot Isolation Not Supported for Distributed Transaction

Why This Error Happens

This error occurs because SQL Server 2025 does not allow the SNAPSHOT or READ COMMITTED SNAPSHOT isolation level to be used once a transaction has been promoted to a distributed transaction via MSDTC (Microsoft Distributed Transaction Coordinator). It is typically triggered when a session with ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT enabled on the database attempts to enlist in a linked server call, a BEGIN DISTRIBUTED TRANSACTION, or a cross-database transaction that spans multiple instances. The underlying engine architecture simply does not support versioning-based consistency models across a two-phase commit boundary.

Step-by-Step Resolution Guide

Step 1: Identify the Query or Procedure Triggering the Error

  • Check the application connection string or session settings for SET TRANSACTION ISOLATION LEVEL SNAPSHOT.
  • Review SQL Server logs and SSMS error output to locate the exact stored procedure or linked server call causing the promotion to a distributed transaction.
  • Use sys.dm_tran_active_transactions and sys.dm_tran_session_transactions to confirm if the transaction is distributed.

Step 2: Change the Isolation Level for the Affected Session

  • Explicitly set the isolation level before the transaction begins:
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  BEGIN TRANSACTION;
  -- your distributed/linked server query here
  COMMIT TRANSACTION;
  • Avoid using READ_COMMITTED_SNAPSHOT or SNAPSHOT for any code path that touches linked servers or BEGIN DISTRIBUTED TRANSACTION.
  • If the database-level default is SNAPSHOT, override it at the session level rather than changing the database default globally.

Step 3: Isolate Distributed Transaction Logic from Snapshot-Dependent Code

  • Refactor stored procedures so that distributed transaction calls run in a separate batch/session that does not inherit SNAPSHOT isolation.
  • If using .NET or ODBC drivers, ensure Enlist=true connections default to READ COMMITTED unless explicitly required otherwise.
  • For linked server queries, confirm remote proc transaction promotion settings via:
  EXEC sp_configure 'remote proc trans', '1';
  RECONFIGURE;

Step 4: Verify MSDTC Configuration on Windows Server 2025 / Windows 11

  • Open Component Services (dcomcnfg.exe) → Computers → My Computer → Distributed Transaction Coordinator → Local DTC.
  • Confirm Network DTC Access is enabled and that “Allow Inbound/Outbound” transactions are permitted if the query spans remote instances.
  • Restart the MSDTC service after any configuration change:
  Restart-Service MSDTC

Alternative or Verification Steps

  • Re-run the failing transaction after setting isolation level to READ COMMITTED or SERIALIZABLE; confirm no Error 3996 appears in SQL Server logs or sys.messages.
  • If the issue persists, avoid distributed transactions altogether by using application-level two-phase logic (e.g., saga pattern) or linked server queries wrapped in local transactions only.