Locking and Transaction support is an important dimension of all the RDBMS. They are optimized to allow lock acquired to be released faster. When there are a large number of DML commands happening on the system, locking and releasing the locks becomes vital part of the whole database. Here I have discussed the reasons for the error related to lock (Microsoft SQL Server Error 1204) and how to fix this issue.
Introduction to SQL Server Error 1204
The error ‘SQL Server database engine cannot obtain a lock resource at this time’ occurs during the execution of a command in the database, because there is not enough locks are available to execute the command. On the other hand we can say that the SQL Server Error 1204 Severity 19 State 4, occurs when there is a memory threshold in the database and the server unable to fetch more memory from the operating system. The max server memory is used by other resources configured on SQL Server.
The message given below comes on the screen when a lock resource problem occurs during the execution of a query:
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.
SQL Server error 1204 severity 19 state 4
Reasons of SQL Server Cannot Obtain a Lock Resource Error
To execute a query there will be a constant need to acquire and release locks. When new locks cannot be acquired because there is no more lock structures available in the pool, the error message occured.
How to Fix Microsoft SQL Server Error 1204
You may encounter this problem when you have set up the sp_configure option "locks" to a non-default, non-dynamic value. The sp_configure system stored procedure can be used to change the value of locks to its default setting by using the following statement:
EXEC sp_configure 'locks', 0If you are getting the error stated above during the use of SQL Server trace flags 1211 or 1224, then please review their use and disable them while executing queries that require a large number of locks. The trace flags 1211 and 1224 are used to control the lock escalation behavior of SQL Server.
To overcome the issue ‘SQL Server database engine cannot obtain a lock resource at this time’ you have two options. Either wait for sometime and re-execute the same command(s), or reconfigure locks value by executing the following commands:
Example:
sp_configure locks,XYZ
where XYZ value higher than the original one
GO
RECONFIGURE_WITH_OVERRIDE
GO For troubleshooting this error, please refer the log and execute the following command:
SELECT @SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name';This will show you the needed info, and you can check Activity Monitor too.
Conclusion
In this article we have discussed about the Microsoft SQL Server error 1204: database engine cannot obtain a lock resource at this time. The error generally occurs when you want to execute a command but there are not enough system locks are available to complete the current command. Here we have also discussed about the reasons behind this error and how can we fix the issue 1204 in SQL Server database.
EmoticonEmoticon