SQL Error 1204 - cannot obtain a LOCK resource at this time

A DBA reported the error message “Error: 1204, Severity: 19, State: 4. 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.”

The message appeared during the daily SQL Server Error log report which checks for SQL Server Error log messages of interest to DBAs.

The SQL error 1204 message appears when memory thresholds are met.

SQL Server cannot obtain more memory from the Operating System. The memory is used by other resources or SQL Server has max server memory configured.

Lock manager doesn’t use more than 60% of memory available to SQL Server

 Possible Fixes

1) Release other applications using resources on the OS – use Task Manager  to identify other applications using the largest amount of memory.  Most DBAs attempt to maintain SQL Server on dedicated servers , but management software can creep in – such as Anti-Virus , monitoring agents and security analysis.

2) Increase the SQL Server max memory setting, if extra memory is available .

3) Use the sys.dm_tran_locks view to identify the session holding the largest amount of locks and kill the process. Before you kill the transaction record the details to

Read More

SQL Server – Find sql server Locks - SQL Server DBA

SQL Server - Use sys.dm_tran_locks to find requests waiting for an exclusive lock

SQL Server Performance ,memory pressure and memory usage