Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to troubleshoot SQL Server lock related problems and errors

How to troubleshoot SQL Server lock related problems and errors

View products that this article applies to.

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.



↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

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:


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









↑ Back to the top

Keywords: kb

↑ Back to the top

Article Info
Article ID : 2199576
Revision : 1
Created on : 1/7/2017
Published on : 4/2/2012
Exists online : False
Views : 261