// SQL Server error reference

SQL Server error 1204: Cannot obtain a LOCK resource

SQL Server ran out of memory for locks. Too many locks are held at once, usually by a huge transaction or a long-running one.

Error number
1204
Severity
19
Category
Memory, log and storage
Error message
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

What it means

Each lock uses a little memory. When SQL Server can't allocate memory for another lock, the statement fails with 1204. Lock escalation normally replaces thousands of row or page locks with one table lock, so this usually means escalation was prevented or the instance is short of memory.

Common causes

  • A single transaction that modifies a very large number of rows while lock escalation is disabled (LOCK_ESCALATION = DISABLE or trace flags 1211/1224).
  • Many concurrent transactions each holding many locks.
  • Low memory available to SQL Server.

How to fix it

  1. Break large modifications into smaller transactions.

    WHILE 1 = 1
    BEGIN
        UPDATE TOP (5000) dbo.Orders SET Archived = 1 WHERE Archived = 0 AND OrderDate < '2023-01-01';
        IF @@ROWCOUNT = 0 BREAK;
    END;
  2. Check lock escalation settings on the table.

    SELECT name, lock_escalation_desc FROM sys.tables WHERE name = N'Orders';
  3. See which sessions hold the most locks.

    SELECT request_session_id, COUNT(*) AS locks
    FROM sys.dm_tran_locks GROUP BY request_session_id ORDER BY locks DESC;

Related errors

More memory, log and storage errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.