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

by

Troubleshooting Guide: SQL Server Error 3983 – “Session Is Not Single Threaded”

Why This Error Happens

Error 3983 occurs when a transaction attempts to run a parallel (multi-threaded) operation while simultaneously participating in a marked transaction or distributed transaction that requires MARS (Multiple Active Result Sets) or single-threaded execution context. This typically happens when a session enlisted in a distributed transaction (via BEGIN DISTRIBUTED TRANSACTION or MSDTC) tries to execute a query that the Query Optimizer decides to parallelize across multiple schedulers. SQL Server 2025 enforces strict thread affinity for transactional consistency, so it blocks the operation rather than risk corrupting the transaction state.

Step-by-Step Resolution Guide

Step 1: Force Single-Threaded Execution via Query Hint

  • Add the OPTION (MAXDOP 1) hint to the specific query causing the failure.
  • Example:
  UPDATE dbo.YourTable
  SET Column1 = 'Value'
  WHERE ID = 1
  OPTION (MAXDOP 1);
  • This forces the Query Optimizer to skip parallel plan generation for that specific statement, bypassing the conflict with the active transaction session.

Step 2: Adjust Server-Level MAXDOP Configuration

  • Open SQL Server Management Studio (SSMS) and connect to the instance.
  • Run the following to check current configuration:
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max degree of parallelism';
  • If the value is 0 (unlimited) and distributed transactions are common in your workload, set it to a fixed low value to reduce parallel plan generation:
  EXEC sp_configure 'max degree of parallelism', 1;
  RECONFIGURE WITH OVERRIDE;
  • Note: This is a server-wide change and may impact performance on other non-transactional workloads—apply with caution on production servers.

Step 3: Review and Refactor Distributed Transaction Code

  • Open the stored procedure or application code that initiates the transaction.
  • Look for BEGIN DISTRIBUTED TRANSACTION or linked server calls combined with complex joins/aggregations.
  • Refactor the code to separate the distributed transaction logic from heavy parallel-eligible queries. Run bulk/parallel-friendly operations before or after the transaction scope rather than inside it.

Step 4: Verify MSDTC Service Configuration (Windows Server 2025 / Windows 11)

  • Open Component Services (dcomcnfg.exe) on the host machine.
  • Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC.
  • Right-click Local DTC, select Properties, and ensure Network DTC Access is enabled if transactions span multiple servers.
  • Restart the Distributed Transaction Coordinator service via services.msc to apply changes.

Alternative or Verification Steps

  • Verify the Fix: Re-run the original transaction/query that threw Error 3983. Check the SQL Server Error Log (Management > SQL Server Logs in SSMS) to confirm no recurrence of Event ID 3983.
  • Alternative Fix: If refactoring code is not feasible, wrap the specific parallel-triggering statement in its own local transaction scope outside the distributed transaction block, committing it independently before the distributed transaction begins.