1204: The instance of the SQL Server Database Engine cannot obtain a LOCK resource

What does it mean?

SQL Server could not allocate the memory the lock manager needs and cannot create more locks. The operation fails because the lock memory pool is exhausted.

Why does it happen?

Too many fine-grained (row/page) locks held at the same time: large, unindexed or bulk DML; lock escalation being prevented; general memory pressure on the server; or long-running open transactions. Occasionally external memory pressure is the trigger.

How to fix it

Split large operations into smaller batches to reduce the number of concurrent locks. Use proper indexes to reduce the number of rows scanned/locked. Review configurations that disable lock escalation: trace flags 1211/1224 and ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE); check with SELECT name, lock_escalation_desc FROM sys.tables; and DBCC TRACESTATUS(-1); Check server memory and the max server memory setting, and relieve memory pressure.