Lock request time out period exceeded

Hi Experts,

We got an issue of high physical memory (98% Used) because of MSSQLSERVER.

Error from event viewer: Lock request time out period exceeded

1. What is the error means ?
2.what can I do to minimize the high usage of memory?

Regards,
Gen.SupIT SupportAsked:
Who is Participating?
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.

lcohanDatabase AnalystCommented:
That meas in my opinion the server does not have MAX memory set so SQL will use as much as possible and even starving the OS and any other apps that need memory. Please have a loog here how to check it and set it to reasonable value and by doing this I believe you get the answer to both questions.

https://www.brentozar.com/blitz/max-memory/
https://www.sqlskills.com/blogs/jonathan/wow-an-online-calculator-to-misconfigure-your-sql-server-memory/

Now...even after that value was set you may get the
Lock request time out period exceeded
error if lets say you add a new index on a large SQL table in SSMS query and from same SSMS session you navigate to DB.table where the index is added and you want to see if it was added ro not - while the CREATE INDEX... is still running - you will get that error for sure.

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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Lock request time out period exceeded
1. What is the error means ?

Error indicates that the table you are trying to access is blocked by some other transaction(s) for a long time..
We need to find the long running transaction or blocking query to have that particular table or object accessed properly.

>> 2.what can I do to minimize the high usage of memory?

I believe the above error has nothing to do with the error message you have received out..
Anyhow, its recommended to configure the Max Memory of SQL Server to some value leaving 4-6 GB RAM for OS and other operations depending whether this server is dedicated for SQL Server alone or not to reduce the High Memory usage by SQL Server service.
FYI, by design SQL Server will try to reserve all available memory in the Server unless Max Server Memory value is configured properly..
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
Microsoft SQL Server

From novice to tech pro — start learning today.