The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time

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

 

Read More

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer