Have you ever had the joy of receiving this 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.

Just a quick edit, the queries contained within this post broke due to some reformatting during a wordpress upgrade.  If you find they are still not working reply in the comments.

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 

SELECT hl.lock_count, hl.request_session_id, s.login_name, s.program_name, s.host_name FROM #holding_locks hl INNER JOIN sys.dm_exec_sessions s ON hl.request_session_id = s.session_id 

SELECT hl.request_session_id, hl.lock_count,  SUBSTRING(st.text, (er.statement_start_offset/2)+1,  ((CASE er.statement_end_offset   WHEN -1 THEN DATALENGTH(st.text)   ELSE er.statement_end_offset           END - er.statement_start_offset)/2) + 1) AS statement_text ,   CONVERT(XML, qp.query_plan) FROM #holding_locks hl INNER JOIN sys.dm_exec_requests er ON er.session_id = hl.request_session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st  CROSS APPLY sys.dm_exec_text_query_plan(er.plan_handle, er.statement_start_offset, er.statement_end_offset) qp ORDER BY lock_count desc 

SELECT hl.request_session_id, hl.lock_count,  SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset  WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text,  CONVERT(XML, qp.query_plan) FROM #holding_locks hl INNER JOIN sys.dm_exec_connections c ON hl.request_session_id = c.session_id INNER JOIN sys.dm_exec_query_stats qs ON qs.sql_handle = c.most_recent_sql_handle CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) qp WHERE NOT EXISTS(SELECT 1 FROM sys.dm_exec_requests er WHERE er.session_id = hl.request_session_id) ORDER BY lock_count desc, qs.statement_start_offset

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.  

 

Watch us diagnose a query timeout with SQLGrease:

See how you can get query level wait events without Query Store: