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

by

Troubleshooting Guide: SQL Server Error 3974 – Exclusive Mode Database Limit Exceeded

Why This Error Happens

This error occurs when the number of databases opened in exclusive mode within a single workspace or session exceeds the internal threshold defined by SQL Server 2025’s resource governor. It is typically triggered by applications or scripts that open database handles without properly releasing locks, orphaned connections from crashed sessions, or a misconfigured workspace pooling setting that caps concurrent exclusive-mode database access. In some cases, corrupted tempdb metadata can also cause SQL Server to miscount active exclusive handles, falsely triggering the limit.

Step-by-Step Resolution Guide

Step 1: Identify Active Exclusive-Mode Sessions

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run the following query to list current sessions holding exclusive locks:
  SELECT request_session_id, resource_database_id, request_mode 
  FROM sys.dm_tran_locks 
  WHERE request_mode = 'X';
  • Note any session IDs (SPIDs) that appear stale or orphaned.

Step 2: Terminate Orphaned or Excessive Sessions

  • For each problematic SPID identified in Step 1, run:
  KILL <session_id>;
  • Alternatively, restart the SQL Server service via Services.msc on Windows Server 2025/Windows 11 to force-release all exclusive handles if KILL commands fail.

Step 3: Adjust Workspace Exclusive Mode Limit

  • Navigate to Computer Configuration > Administrative Templates > SQL Server > Workspace Settings in the Local Group Policy Editor (gpedit.msc), or edit the instance-level configuration directly.
  • Modify the max exclusive db handles setting using:
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max worker threads', 0; -- allows auto-scaling
  RECONFIGURE;
  • Restart the SQL Server service for changes to take effect.

Step 4: Check and Repair tempdb Metadata

  • Run DBCC CHECKDB (tempdb) to rule out corruption affecting session counting.
  • If corruption is found, restart the instance to rebuild tempdb automatically, as tempdb is recreated on every SQL Server startup.

Alternative or Verification Steps

  • After applying fixes, re-run the application workload that originally triggered Error 3974 and monitor sys.dm_tran_locks to confirm exclusive-mode session counts stay below the configured threshold.
  • If the error persists, apply the latest Cumulative Update (CU) for SQL Server 2025, as workspace handle-counting bugs are occasionally addressed in servicing releases.