Locking problem in SQL Server 2016

"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. [SQLSTATE HY000] (Error 1204).  The step failed."

default lock set to '0'

This is sql server 2016
VijayAsked:
Who is Participating?
 
VijayConnect With a Mentor Author Commented:
Sorry for the delay. We identified that, there is a query which was causing this issue. After adding OPTION (MAXDOP 1). Every thing went fine.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Kindly check for any long running queries or transactions and clear those to have the new sessions connect without any issues.
0
 
pcelbaCommented:
What edition of SQL 2016 do you use?
How much memory does the SQL Server have available?
How many concurrent users/applications are working with this SQL Server instance?

What is the result of the following query?
SELECT request_session_id, COUNT(*)
FROM sys.dm_tran_locks
GROUP BY request_session_id 
ORDER BY count(*) DESC

Open in new window

Does some session have too many locks acquired?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
VijayAuthor Commented:
This is SQL Server 2016 Enterprise Edition.
this is physical server contians 320 GB RAM.
Please find the attachment for user connections and query out put.
locks-results.txt
connections.txt
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
this is physical server contians 320 GB RAM.
But how much is reserved for the SQL Server instance? Check for the Max Server Memory configuration.
0
 
pcelbaCommented:
There is something wrong with the first 3 sessions. Please look what applications are connected to those sessions if such info is available in e.g. sp_who2 and then analyze these applications and running transactions.

If you encountered the above error message when using the SQL Server trace flags 1211 or 1224, please review their use and disable them while executing queries that require a large number of locks. Trace flags 1211 and 1224 are used to control the lock escalation behavior of the SQL Server. More info: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql
More about locks escalation: https://msdn.microsoft.com/en-us/library/ms184286.aspx

The quick and dirty solution is to kill sessions with the extraordinary number of locks BUT that could cause problems in your application and/or it does not solve your problem in a long perspective...
0
 
pcelbaCommented:
Hi Vijay,

any news in the locks escalation? We should make some progress in this question.

TIA
Pavel
0
 
pcelbaCommented:
MAXDOP should not affect the number of locks in such a huge way. OTOH, I don't know the parallelism before this change so everything is possible and great thing is you've solved it.

Could you please post the query here? Just to have a picture what everything can happen.

TIA
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
After adding OPTION (MAXDOP 1).
What's the default MAXDOP for your SQL Server instance? You can see that by running the following command:
sp_configure 'max degree of parallelism'

Open in new window

0
 
VijayAuthor Commented:
Resolved my Self.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.