How to troubleshoot SQL Server lock related problems and errors

Article ID: 2199576 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

You might encounter the SQL Server error message 1204 when your application executes a TSQL statement that requires a large number of locks: 
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.



CAUSE

While executing queries there will be a constant need to acquire and release locks. Acquirng a lock uses up the the lock structures from the available pool of lock structures. When new locks cannot be acquired because there is no more lock structures available in the pool, the above error message is returned.

RESOLUTION

You can encounter this problem when you have setup the sp_configure option "locks" to a non-default, non-dynamic value. You can use the sp_configure system stored procedure to change the value of locks to its default setting by using the following statement:
EXEC sp_configure 'locks', 0


If you encountered the above error message when using the SQL Server trace flags 1211 or 1224, please review thier 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 INFORMATION

locks Option
Lock Escalation (Database Engine)
Trace Flags (Transact-SQL)

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:


Collapse this tableExpand this table
Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated 
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)





Locks Configuration Not Dynamic







The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect situations where the configuration option "locks" is not set to a default value. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2. 

If you run the BPA tool and encounter a warning with the title of Database Engine - Locks Configuration Not Dynamic, then you need to check the value currently configured for the "locks" option in sp_configure result set and follow the recommendations to reset the configuration value to its default.
SQL Server 2008
SQL Server 2008 R2







SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)





Locks Configuration Not Dynamic






The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect situations where the configuration option "locks" is not set to a default value.

If you run the BPA tool and encounter a warning with the title of Database Engine - Locks Configuration Not Dynamic, then you need to check the value currently configured for the "locks" option in sp_configure result set and follow the recommendations to reset the configuration value to its default.
SQL Server 2012









Properties

Article ID: 2199576 - Last Review: April 2, 2012 - Revision: 3.0
APPLIES TO
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Enterprise
Keywords: 
KB2199576

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com