SQL Server Error 1204

Error number

1204

Severity

19 (resource or software problem)

State

Not documented by Microsoft (not reproduced here)

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Query Processing & Performance

Last reviewed

24 October 2026

What it means

Error 1204 means SQL Server could not allocate memory for one more lock, because the pool of lock structures for the whole instance is exhausted, so the statement that needed the lock was stopped.

Every lock a query takes uses a small lock structure from a shared pool. When no more structures can be allocated, the statement that asked for one fails with 1204 at severity 19, a resource error: it is about the instance, not about your data or your permissions. Microsoft lists the reasons: SQL Server cannot get more memory (other processes use it, or max server memory is reached), the lock manager has reached its ceiling of 60 percent of the memory available to SQL Server, the locks option was set to a fixed non-default value, or trace flags 1211 or 1224 limit lock escalation while queries take very many locks.

In practice one session holding millions of row or page locks is the usual trigger: a large update or delete in a single transaction on a table where lock escalation cannot happen, or a long-running open transaction that keeps acquiring locks. The failing statement may belong to an innocent user; look for the session that holds the most locks.

Common causes

• A single transaction that modifies millions of rows while lock escalation is prevented (table option LOCK_ESCALATION = DISABLE, trace flag 1211 or 1224, or concurrent access that blocks escalation).
• A long-running or forgotten open transaction that keeps accumulating locks.
• The locks server option set to a fixed number instead of 0 (dynamic).
• Memory pressure: max server memory is low or other memory clerks (or other processes on the server) take the memory locks would need.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

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

SELECT type, name, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = N'OBJECTSTORE_LOCK_MANAGER'
ORDER BY pages_kb DESC;

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN (N'locks', N'max server memory (MB)', N'min server memory (MB)');

SELECT TOP (10) st.session_id, at.transaction_begin_time,
       DATEDIFF(MINUTE, at.transaction_begin_time, SYSDATETIME()) AS open_minutes,
       s.login_name, s.program_name
FROM sys.dm_tran_active_transactions AS at
JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = at.transaction_id
JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id
ORDER BY at.transaction_begin_time;

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name, name, lock_escalation_desc
FROM sys.tables
WHERE lock_escalation_desc = N'DISABLE';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

DECLARE @rows int = 1;
WHILE @rows > 0
BEGIN
    DELETE TOP (5000) FROM dbo.AuditLog
    WHERE logged_at < '2025-01-01';
    SET @rows = @@ROWCOUNT;
END;

-- after confirming session 87 is the culprit and may be stopped
KILL 87;

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'locks', 0;
RECONFIGURE;

ALTER TABLE dbo.AuditLog SET (LOCK_ESCALATION = TABLE);

Examples

A huge delete on a table that cannot escalate locks

Shown as documented by Microsoft; not reproduced here, because exhausting lock memory affects every session on the instance. Whether this statement fails depends on the number of rows, other sessions and available memory. The state is not documented, so no message header is shown.

ALTER TABLE dbo.AuditLog SET (LOCK_ESCALATION = DISABLE);

BEGIN TRANSACTION;
    DELETE FROM dbo.AuditLog WHERE logged_at < '2025-01-01';   -- tens of millions of rows
COMMIT;

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.

How to prevent it

Write maintenance jobs (purges, archiving, mass updates) to work in batches, and keep transactions short in application code. Leave the locks option at 0 and lock escalation at its default unless a measured problem requires otherwise, and document any exception.

Alert on very long open transactions and on sessions holding unusually many locks, so you can act before the lock pool runs out. The transactions, locks and deadlocks lesson explains how locks are taken and released.

Common questions

Learn more on CodeWithSQL

• Transactions, locks and deadlocks
• Managing transactions and concurrency

Official documentation

• MSSQLSERVER_1204 (Microsoft Learn)
• Configure the locks server configuration option
• Transaction locking and row versioning guide
• sys.dm_tran_locks (Transact-SQL)

Last technically reviewed 24 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.