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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

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
VijayAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.