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

by

Troubleshooting Guide: SQL Server Error 1221 — “Database Engine is attempting to release a group of locks that are not currently held by the transaction”

Why This Error Happens

This error typically occurs due to internal lock manager inconsistencies, often triggered by deadlock resolution conflicts, corrupted lock structures in memory, or a transaction attempting to release locks that were already released by a prior rollback operation. It can also surface when third-party backup tools, antivirus software, or filter drivers interfere with SQL Server’s I/O operations, causing transaction state mismatches. In rare cases, this is a symptom of underlying database corruption or outdated SQL Server build-level bugs related to lock escalation logic.

Step-by-Step Resolution Guide

Step 1: Apply the Latest SQL Server 2025 Cumulative Update

  • Navigate to Microsoft’s official download center and check the current Cumulative Update (CU) level for SQL Server 2025.
  • Run SELECT @@VERSION; in SSMS to confirm your current build number.
  • Install the latest CU, as lock manager bugs are frequently patched in servicing releases.
  • Reboot the server after installation: Restart-Computer -Force (via PowerShell as Administrator).

Step 2: Check for Antivirus or Filter Driver Interference

  • Open Windows Security or your third-party AV console and add exclusions for SQL Server data/log directories (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
  • Exclude the sqlservr.exe process from real-time scanning.
  • Use fltmc.exe in an elevated Command Prompt to list active filter drivers and identify any non-Microsoft drivers attached to the volume hosting your database files.
  • Temporarily disable third-party backup agents (e.g., VSS-based tools) to rule out I/O conflicts.

Step 3: Run DBCC CHECKDB to Rule Out Corruption

  • Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; in SSMS.
  • Review the output for allocation or consistency errors tied to the affected tables or indexes.
  • If corruption is found, restore from the most recent clean backup or use DBCC CHECKDB('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) only as a last resort in a maintenance window.

Step 4: Review and Adjust Lock Escalation Settings

  • Query current lock escalation behavior: SELECT name, value FROM sys.configurations WHERE name = 'locks';
  • If the server has a fixed, low locks configuration value, reset it to dynamic allocation: EXEC sp_configure 'locks', 0; RECONFIGURE;
  • Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER -Force for the change to take effect.

Step 5: Enable Trace Flags for Deeper Diagnostics

  • Add Trace Flag 1211 or 1224 (lock escalation control) temporarily in SQL Server Configuration Manager under Startup Parameters (e.g., -T1211).
  • Capture a SQL Server Profiler or Extended Events trace filtered on Error 1221 to identify the exact query/transaction triggering the issue.
  • Remove the trace flag once root cause is identified, as it can impact performance long-term.

Alternative or Verification Steps

  • Re-run the previously failing transaction/workload under a monitored session and confirm Error 1221 no longer appears in the SQL Server Error Log (EXEC sp_readerrorlog;).
  • If the issue persists after patching and corruption checks, open a Microsoft Support (CSS) case and provide a SQL Server memory dump generated at the time of failure for deep lock-manager analysis.