Diagnosing: The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time

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.

If you are receiving this message, it means that SQL Server has reached the maximum amount of memory it can allocate for locks. To see how much memory is consumed by locks you can query sys.dm_exec_memory_clerks. This query varies by whether you are on sql server 2008 or later.

SQL Server 2012 or later:

SELECT SUM(pages_kb)/1024 AS lock_memory_megabytes, type FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY 1 DESC

SQL Server 2008:

SELECT (SUM(single_pages_kb) + SUM(multi_pages_kb))/1024 AS lock_memory_megabytes, type FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY 1 DESC

The output of this shows we have approximately 4.2 GB consumed by locks (OBJECTSTORE_LOCK_MANAGER). This is approximately 40% of what is configured for the max memory setting for this instance (I lowered the memory to generate the error message above).

We’ll need to see which sessions are holding the most locks. Use the following three queries to find which sessions are holding the most locks. This set of queries returns 3 sets of results: The session information, actively executing sessions holding locks, and idle sessions holding locks. Run all the following set of statements together in SSMS:

SELECT TOP 10 count(1) AS lock_count, request_session_id INTO #holding_locks FROM sys.dm_tran_locks GROUP BY request_session_id ORDER BY 1 DESC

The output of this query shows which sessions/queries are holding the most locks.

The first set of results contains session info. We can see that session_id 78 is holding the most locks. The next set of results contains actively executing sessions holding the locks. This also contains the statement that the sessions are currently executing as well as the execution plan. The third set of results contains idle sessions that are currently holding locks. There are no idle sessions holding locks in this example.

Based on this, it would appear the statement likely generating the high lock memory usage would be the following:

update CustomerTest with(rowlock) set ModifiedDate = getdate()

Note the rowlock hint. I purposely (for the sake of example) added this hint in order to generate a higher number of locks than otherwise would have been generated.

It’s important to note the queries I provided include the execution plans of the queries involved. The reason this is important is bad execution plans can generate large numbers of locks unnecessarily resulting in exhaustion of lock memory.