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

by

Troubleshooting Guide: Error 3956 – Snapshot Isolation Transaction Failed to Start

Why This Error Happens

Error 3956 occurs because the ALTER DATABASE command used to enable ALLOW_SNAPSHOT_ISOLATION is an asynchronous operation that must wait for all existing active transactions in the database to commit before it can finalize the state change to ON. If a new transaction attempts to start a snapshot isolation level while the database is still in this “pending ON” transitional phase, SQL Server 2025 rejects it because the versioning infrastructure is not yet fully active. This is typically caused by long-running transactions or blocking sessions that were active at the exact moment the ALTER DATABASE command was issued, preventing the state transition from completing quickly.

Step-by-Step Resolution Guide

Step 1: Identify Blocking or Long-Running Transactions

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
  • Run the following query to check the current state of snapshot isolation for the specific database:
  SELECT name, snapshot_isolation_state, snapshot_isolation_state_desc 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';
  • Look for a snapshot_isolation_state value of 2 (INTRANSITIONTO_ON), which confirms the pending status.

Step 2: Locate and Terminate Blocking Sessions

  • Query sys.dm_tran_active_transactions and sys.dm_exec_sessions to find any open transactions preventing the version store from initializing.
  • Use the following command to check for active requests against the specific database ID:
  SELECT * FROM sys.dm_exec_requests WHERE database_id = DB_ID('YourDatabaseName');
  • If a specific Session ID (SPID) is holding an old transaction open (common with ORM tools like Entity Framework or legacy JDBC drivers), terminate it using KILL [SPID].

Step 3: Wait for Transition or Restart the Alter Command

  • Allow a grace period of 30–60 seconds after killing blocking processes, as the background mechanism needs time to loop through and verify the Transaction Log Sequence Number (LSN).
  • If the state remains stuck, re-issue the command manually via a new query window to force a re-check (ensure no other session is currently running this same command):
  ALTER DATABASE YourDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;
  • Check the SQL Server Error Log via SQL Server Configuration Manager > Logs for any underlying I/O or Tempdb contention errors that might be stalling the version store creation on the Windows Server 2025 host.

Step 4: Verify Tempdb Health and Capacity

  • Navigate to the Tempdb file location (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) to ensure there is sufficient disk space.
  • Since snapshot isolation relies heavily on the version store located in Tempdb, verify that Tempdb has not reached its maximum size limit, as this will stall the ALTER DATABASE transition indefinitely.
  • Expand Tempdb files manually if necessary using ALTER DATABASE tempdb MODIFY FILE.

Alternative or Verification Steps

  • Verification: Re-run the sys.databases query from Step 1; a successful fix will show snapshot_isolation_state_desc as ON rather than IN_TRANSITION_TO_ON.
  • Alternative: If the issue persists due to a stuck system process, consider scheduling the ALTER DATABASE command during a maintenance window with SINGLE_USER mode to guarantee no conflicting transactions block the state change.