SQL Server Cannot Obtain a LOCK Resource at This Time (Error 1204)

A statement that ran yesterday has come back with error 1204 and rolled itself back. Nothing is corrupt, nothing is deadlocked, and the instance is still up and serving everybody else. SQL Server ran out of lock structures part-way through your statement and abandoned it rather than carry on without locks. On an instance left at its defaults this is rare, and that rarity is the clue: something has put a ceiling on the lock manager, and 3 things can do it.

Start Here: Is locks Set to Anything Other Than 0

One query, and it answers the configured value and the running value separately, which matters more than it looks.

SELECT name,
       CAST(value AS bigint)        AS value,
       CAST(value_in_use AS bigint) AS value_in_use,
       minimum,
       maximum
FROM   sys.configurations
WHERE  name = 'locks';

name   value  value_in_use  minimum  maximum
-----  -----  ------------  -------  ----------
locks  0      0             5000     2147483647

value_in_use is what the running instance is actually enforcing. value is what it will enforce after the next restart. Those two disagreeing is normal and expected the moment somebody changes the setting, because MS Docs is explicit that the server must be restarted before a change to locks takes effect. So a reading of 0 in value and 5000 in value_in_use means somebody has already fixed this and nobody has restarted yet.

The same page is worth reading for 2 other reasons. The minimum you are allowed to set is 5000, so there is no such thing as a gently conservative value here. And the option is marked for removal in a future version of SQL Server, which tells you what Microsoft thinks of it: a non-zero locks is almost always a decision somebody made in 2009 and nobody has revisited.

The 1 Line in the ERRORLOG That Settles It

SQL Server writes which lock allocation strategy it is using every time it starts, so you can answer the question from the log without a connection and without permission to read sys.configurations. The 2 forms of the line are the whole diagnosis:

locks = 0 (the default)
21:59:58.17 Server   Using dynamic lock allocation.  Initial allocation of 2500 Lock
                     blocks and 5000 Lock Owner blocks per node.

locks = 5000 (capped)
21:37:07.70 Server   Using the static lock allocation specified in the locks
                     configuration option.  Allocated 5000 Lock blocks and 5000 Lock
                     Owner blocks per node.

The word to look for is “static”. If the line says static, the number beside it is a hard ceiling for the whole instance and you have found your cause. If it says dynamic, the initial pool is 2500 lock blocks and SQL Server will grow it as needed, so a 1204 means the growth hit a different wall and memory is where to look.

Find the Transaction Holding the Locks

MS Docs gives the short version of this on its own page for error 1204: count locks per session, take the top one, and kill it if you have to. That is the right emergency move. The version below adds the resource type, which is what tells you why that session is holding so many.

-- Who is holding what, grouped so the shape of the problem is obvious
SELECT request_session_id,
       resource_type,
       request_mode,
       COUNT(*) AS locks_held
FROM   sys.dm_tran_locks
GROUP  BY request_session_id, resource_type, request_mode
ORDER  BY COUNT(*) DESC;

resource_type  request_mode  locks_held
-------------  ------------  ----------
KEY            X             1200
PAGE           IX            5
DATABASE       S             1
OBJECT         IX            1

Read the KEY row. 1 exclusive KEY lock per row touched, and the count tracks the rows the statement has modified so far, so a session sitting on several hundred thousand of them is a single statement that nobody batched. The OBJECT row is the one to watch for the opposite reason: when it reads X instead of IX, escalation has already happened and the table is locked whole. MS Docs documents every value in that column on sys.dm_tran_locks, and it is worth a bookmark because the mode abbreviations are not guessable.

Check the Lock Manager’s Memory

Locks are memory. Every one of them costs 96 bytes, a figure MS Docs states on the locks page, and they all come out of 1 memory clerk:

SELECT type,
       pages_kb,
       virtual_memory_committed_kb
FROM   sys.dm_os_memory_clerks
WHERE  type = 'OBJECTSTORE_LOCK_MANAGER';

type                      pages_kb  virtual_memory_committed_kb
------------------------  --------  ---------------------------
OBJECTSTORE_LOCK_MANAGER  57992     0
OBJECTSTORE_LOCK_MANAGER  24        0

That is about 57 MB of lock structures for 1 UPDATE, and it lines up with the arithmetic: 300,000 row locks at 96 bytes each is roughly 28 MB before you count the page and object locks and the owner blocks that go with them. SQL Server reports this clerk across more than 1 row, so add them up rather than reading the first.

The limit that matters is a proportion, not a number. MS Docs states that the dynamic lock pool will not take more than 60 percent of the memory allocated to the Database Engine, and that further lock requests fail once it does. So on a busy instance with a low max server memory, a single unbatched statement can produce 1204 with locks sitting at its default. If this clerk is large and something else on the instance is larger, the memory configuration is the next thing to read, not the lock settings.

Lock Escalation, and What Turning It Off Costs You

Lock escalation is the feature that normally makes 1204 impossible. MS Docs puts the thresholds plainly in Resolve blocking problems caused by lock escalation: once a statement holds more than 5,000 locks on 1 table or index, or lock memory reaches 40 percent of the memory available to the lock manager, SQL Server swaps the lot for a single table lock. That conversion is why a 10 million row DELETE does not exhaust the lock manager on a default instance.

Which means 1204 on an instance with locks at 0 is nearly always somebody having switched escalation off. There are 2 ways to do that and both leave evidence. At table level it is ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE), which MS Docs describes on ALTER TABLE as preventing escalation “in most cases”, with TABLE as the default. Instance-wide it is trace flag 1211 or 1224. This query finds the table-level ones:

-- Any table not on the default. Run per database.
SELECT s.name AS schema_name,
       t.name AS table_name,
       t.lock_escalation_desc
FROM   sys.tables  AS t
JOIN   sys.schemas AS s ON s.schema_id = t.schema_id
WHERE  t.lock_escalation_desc <> 'TABLE'
ORDER  BY s.name, t.name;

On the instance behind this post it returned exactly 1 row, the table the statement was updating, reading DISABLE. For the trace flags, Get Trace Flags and Resource Governor Configuration reports what is on globally and at session scope, and it names 1211 and 1224 for what they are. MS Docs’ own advice is that 1211 is the worse of the 2 because it disables escalation even under memory pressure, so if either is on and nobody can say why, that is the finding.

One trap worth stating because it looks like a fix and is not: a ROWLOCK hint does not prevent escalation. MS Docs says lock hints only alter the initial lock plan. A hint cannot save a statement that is heading for 1204, and it cannot cause one either.

The Reproduction, and What the Log Recorded

Run on SQL Server 2022 CU27, build 16.0.4295.3, on an instance deliberately set to locks = 5000 and restarted, against a 300,000 row table with LOCK_ESCALATION = DISABLE. A single UPDATE inside 1 transaction was enough. The client saw Msg 1204 and the transaction rolled back, and the ERRORLOG recorded it too, which is the part people miss:

21:38:19.22 spid74   Error: 1204, Severity: 19, State: 2.
21:38:19.22 spid74   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.
21:38:32.23 spid74   Error: 1204, Severity: 19, State: 2.
21:38:32.23 spid74   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.

That matters for 2 reasons. It gives you the spid and the time after the fact, when the application has only told you something failed overnight. And because it is logged, Get Recent Error Log Entries will surface it on an instance nobody was watching.

Where the Ceiling Actually Sat

With locks at 5000 the limit arrives far earlier than the number suggests, because a configured lock is not the same thing as a row. The same UPDATE was run at increasing sizes, each in its own transaction:

So a setting that reads 5000 gave out somewhere between 3,000 and 3,500 rows. The gap is the rest of the instance: page locks, object and database locks, the lock owner blocks, and every other session’s locks competing for the same fixed pool. That is the practical reason raising the number is not the fix. You cannot calculate a safe value, because it depends on what everyone else is doing at the time.

For contrast, the same 300,000 row UPDATE at locks = 0 took 300,000 KEY locks and 1,150 page locks and ran to completion with no error, with the lock manager clerk at about 57 MB. The ceiling was never the workload.

The Fix, in the Order Worth Trying

1. Batch the statement. This is the only fix that needs nobody’s permission and no restart, and MS Docs recommends it ahead of everything else. Batching the 300,000 row UPDATE into 1,000 row chunks ran as 300 batches, closed all 300,000 rows, and never came close to the ceiling, because each batch commits and releases its locks before the next one starts.

DECLARE @rows int = 1;

WHILE @rows > 0
BEGIN
    UPDATE TOP (1000) dbo.OrderLine
       SET Status = 'CLOSED'
     WHERE Status = 'OPEN';

    SET @rows = @@ROWCOUNT;
END

Mind the WHERE clause: the loop needs a predicate the update itself falsifies, or it runs forever. Deleting Rows in Batches in SQL Server covers the same pattern for deletes, including the log growth question that batching is really there to solve.

2. Put lock escalation back. If a table is sitting on DISABLE and nobody can name the blocking problem it was switched off to cure, put it back and the problem disappears on its own. On the reproduction, SET (LOCK_ESCALATION = TABLE) took the same 300,000 row UPDATE from 300,000 locks to 2: 1 shared database lock and 1 exclusive object lock. The cost is real and it is the reason somebody turned it off, since that exclusive table lock blocks every other reader and writer for the duration. The honest trade is to batch the statement and leave escalation on, which gets you short locks instead of 1 long one.

3. Put locks back to 0. If the check at the top of this page found a non-zero value, this is the real repair, and it needs a restart to take effect. Treat it as a change with a maintenance window rather than something to run during the incident, and use the batching fix to get through tonight.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

EXEC sp_configure 'locks', 0;
RECONFIGURE;
GO
-- value_in_use will still show the old number until the instance restarts.

After the restart, confirm it took by looking for the word “dynamic” in the ERRORLOG line rather than by re-reading sys.configurations. The log is the instance telling you what it is doing; the configuration view is only telling you what it was asked to do.

Error 1204 Is Not Trace Flag 1204

Searching for “1204 SQL Server” returns 2 completely unrelated things, and the trace flag is the louder of the 2. Trace flag 1204, in MS Docs’ own words on the trace flags page, “returns the resources and types of locks participating in a deadlock and also the current command affected”. It is a deadlock diagnostic. It has no connection to the lock manager running out of structures, turning it on will tell you nothing about this error, and Microsoft advises against leaving it on anyway.

The flags that are relevant to error 1204 are 1211 and 1224, which disable lock escalation, and Get Trace Flags and Resource Governor Configuration lists all 3 with what each one does, so you can see at a glance which of them is actually on. If you arrived here wanting deadlock output, Troubleshoot SQL Server Blocking, Step by Step is the page you want instead.

Frequently Asked Questions