Article ID: 2199576 - View products that this article applies to.
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.
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.
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.
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