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

by

Troubleshooting Guide: SQL Server Error 1220 – “No More Lock Classes Available”

Why This Error Happens

Error 1220 occurs when a single transaction exhausts the internal lock owner blocks (lock classes) that SQL Server allocates to track locks within that transaction’s scope. This typically happens when a transaction performs an extremely high number of operations (bulk inserts, updates, or deletes) without intermediate commits, causing the internal lock management structures to overflow. It can also surface when the server is under severe memory pressure, limiting the resources available for the Lock Manager to allocate new lock classes dynamically.

Step-by-Step Resolution Guide

Step 1: Break Up the Large Transaction

  • Identify the query or stored procedure reported in the error (check SQL Server Error Log or Extended Events for the associated Session ID).
  • Refactor large batch operations (e.g., DELETE, UPDATE, INSERT) into smaller chunks using a loop with TOP (n) and explicit COMMIT statements.
  • Example pattern:
  WHILE 1 = 1
  BEGIN
      DELETE TOP (10000) FROM LargeTable WHERE Condition = 1;
      IF @@ROWCOUNT = 0 BREAK;
  END

Step 2: Review and Increase the ‘locks’ Configuration Option

  • Although SQL Server dynamically manages locks by default, verify the locks server configuration hasn’t been manually capped.
  • Run the following in SSMS to check current settings:
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'locks';
  • If the value is non-zero (indicating a fixed limit), reset it to 0 to allow dynamic allocation:
  EXEC sp_configure 'locks', 0;
  RECONFIGURE;
  • Restart the SQL Server service via services.msc or Computer Management on Windows Server 2025/Windows 11 for the change to take effect.

Step 3: Check System Memory Allocation

  • Navigate to Task Manager > Performance > Memory to ensure the OS isn’t starving SQL Server of RAM.
  • In SSMS, verify the max server memory setting isn’t too restrictive relative to the physical hardware:
  EXEC sp_configure 'max server memory (MB)';
  • Increase the allocated memory if the server has available physical RAM headroom, then apply via RECONFIGURE.

Step 4: Apply Latest Cumulative Updates

  • Navigate to the Microsoft Update Catalog or use Windows Update settings to ensure Windows Server 2025 is fully patched.
  • Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Download Center to ensure known Lock Manager bugs are patched.
  • Reboot the server post-installation to finalize the patch application.

Alternative or Verification Steps

  • Verification: Re-run the previously failing transaction/batch job and monitor sys.dm_tran_locks in a separate query window to confirm lock counts remain stable and do not spike uncontrollably.
  • Alternative: If chunking the transaction is not feasible immediately, temporarily isolate the workload to a session with SET DEADLOCK_PRIORITY LOW and monitor via Performance Monitor (PerfMon) using the SQLServer:Locks counter to catch the threshold before it errors out again.