I've recently started getting this error :
Msg 1204, Level 19, State 4, Line 1
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
on my development SQL Server instance - full version (
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
Initially this happened with one database, but now it affects all of them (27 of them), so my intuition says it's an instance related problem.
What I've tried and the order in which I tried to solve this is enumerated below:
- I've checked long-running queries with sp_whoisactive and nothing comes up as suspicious, actually there are little to no queries running on the instance when I replicated the issue.
Initially this came up once every now and then, and I couldn't replicate it a second time. Now this occurs for every query / stored procedure etc. that needs some reasonable amount of memory (a few MB of data to be returned).
For example, running a query like below will work:
SELECT ID FROM Table WHERE Description = 'Material & Delivery' -- (0 rows affected)
But running a query like below won't work and will throw the above mentioned error:
SELECT * FROM Table WHERE Description = 'Material & Delivery'
This kind of indicates to me that for some reason SQL Server can't allocate sufficient memory to the query, either there's not enough memory available or I'm assuming the memory grant requested is larger than available memory (so a spill to disk will occur?, but I didn't manage to look into this -> the execution plan is not displayed because I get the error).
I looked at the table into more detail and noticed it didn't have a clustered index, so I thought to create one. But when running the create index command I got the
Based on this answer I though to make a change to the lock settings at instance level and changed the number of locks from 5000 to 20.000 with the below code, but this had no effect:
sp_configure 'locks', '20000'; go reconfigure; go
Also, I had a look into
sys.dm_tran_locksto see if there are any locks, but the result set is empty.
I mentioned above that I was also getting this error when running a stored procedure and I looked into the queries inside and added
(NOLOCK)hints eveywhere, but this didn't change anything.
I restarted the instance hoping to clear out all memory and maybe orphaned sessions that maybe had a lock or were hogging resources and did not appear in
sys.dm_tran_locks- idea based on #2 from this link.
I'm kind of stumped and I don't really know what to check next or what could be the cause of this. I'm going to continue digging but any help is very appreciated.
half solved - read below):
- I wanted to share the execution plan with you all and noticed that in both cases a Table Scan was occurring, but it was running in parallel. Then I decided to change instance-level settings from MAXDOP 8 to MAXDOP 1 and hurrah this made my second query run.
When attempting to also test the stored procedure I got the
LOCK error again, and at that point I realized that I had some
OPTION (MAXDOP 6) hints on some queries.
I tried to bring them down to
MAXDOP 2 since I knew query level hints overwrite instance settings but I still got the
The next step was kind of obvious, remove the
MAXDOP hints altogether and this solved my stored procedure problems.
Now, I added those hints to the queries for a reason and would very much like them to stay there, but finding out why and how are they impacting the instance is my next goal.
It seems that even though parallelism is set to 1 and
MAXDOP hints were commented out, this solved the problem for a while. Now I'm getting the
LOCK errors again.
- Tried to clean memory with DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE, but without success. I also checked the locks again, nothing suspicious so far.
These are my top WAIT STATS, as Erik suggested:
Running just the query is fine, but running the stored procedure with commented
MAXDOP hints still throws the
LOCK error. Resource Monitor shows however that there are 110 MB free.