15 May,2012 by Tom Collins
Question: This SQL Server Error message is appearing on a SQL Server Production Server Error Logs :
“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.”
How can I troubleshoot this error ?
Answer: Each lock that SQL takes out uses up a small amount of memory. Upper limit on locks can be set , but the default is to set dynamic lock escalation.
This message is triggered when : a) When a single Transact-SQL statement acquires 5,000 locks on a single table or index.
b) When the number of locks in an instance of the Database Engine exceeds memory i.e 60% of Max server memory) or SQL Server lock configuration thresholds.
1) step is to identify the queries running at the time of this message. Analyse these queries a) Can they be tuned for lesser locks b) Can they be tuned to run in a shorter time?
2) Check for excessive fragmentation levels on tables? Check SQL Index Fragmentation and sys.dm_db_index_physical_stats
3) Does the instance have enough memory ? Check SQL Server memory configuration management
Find queries causing Lock Timeouts per second - SQL Server DBA
SQL Server – Find sql server Locks - SQL Server DBA
SQL Server – LCK_M_X and how to reduce it - SQL Server DBA
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |