Dear Group:
I am not sure if this belongs here, or in the Administration section, so if I should move it, please let me know.
We have the following databaes: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
What is happening is every night, a different team runs their processes to push data to our server. However, they keep getting errors stating "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".
We notice that their process uses about 90 connections to push the data. However, when I right mouse click on the server, click "Properties" and go to "Connections", it shows "Maximum number of concurrent connections" as zero, which is unlimited, so the number of connections shouldn't be an issue.
When I check for locks in the morning, I am not seeing any locks. I am trying to figure out how to see historical locks, but having an issue with this. Also, when I run the following query (SELECT SUM(pages_kb)/1024 AS lock_memory_megabytes, type FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY 1 DESC), I see the following: 28450 OBJECTSTORE_LOCK_MANAGER. This seems to be high based on my Google searches, but having trouble finding what to do.
So I am hoping for some help on what might be causing this and how to trouble shoot this, as unfortunately, the two of us are not DBAs to really know about the deep dive SQL Server administration stuff. There might be more I am missing to say, so please let me know as well.
Thank you for any help
Andy
February 15, 2022 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
I think I read the same page as you did, based on the query you posted. It indicates that the problem is that the amount of memory available for locks has been exceeded. Can you see how much memory the server has and how much SQL Server is allowed to use? If you can't increase either of these I suggest you ask the other team to run fewer tasks in parallel.
You can also use Extended Events to record locks. I don't know much about this, but this link appears to have instructions that may meet your needs. https://www.mssqltips.com/sqlservertip/5752/configuring-extended-events-to-find-locking-issues-in-sql-server/
Edit: Have you checked the Locks configuration in the Instance Properties / Advanced? If the value is not zero, then someone has limited the number of locks. I don't know if this leads to the same error message, but it's worth checking.
February 16, 2022 at 7:05 pm
Take a look at this post.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply