15 May,2012 by Jack Vamvas
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