3

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 (SELECT @@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:

  1. 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).

  1. 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 LOCK error again.

  2. 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

  3. Also, I had a look into sys.dm_tran_locks to see if there are any locks, but the result set is empty.

  4. 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.

  5. 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.

Update (problem half solved - read below):

  1. 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 LOCK error. 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.

Update #2:

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.

  1. Tried to clean memory with DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE, but without success. I also checked the locks again, nothing suspicious so far.

Update #3:

These are my top WAIT STATS, as Erik suggested:

enter image description here

Update #4:

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.

enter image description here

19
  • How much memory has your instance?
    – sepupic
    Oct 24 '17 at 10:54
  • @sepupic 8GB of RAM (initially it was 4GB and when i started getting the problem I bumped it up to 8). Oct 24 '17 at 10:59
  • Are other users accessing this server, or is it just you? Oct 24 '17 at 12:14
  • @sp_BlitzErik Just me at the moment. I have some devs that are connected to this instance and debugging the application, but there's a query every 5-10 minutes.. maybe. sp_whoisactive bearly shows more than 1 query running every now and then. Oct 24 '17 at 12:15
  • 1
    Interesting. If you don't already have sp_BlitzFirst on there, can you grab it? Just run it with @SinceStartup = 1 and update your question with the top wait stats. Oct 24 '17 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.