When we see the error 1204 in the SQL Server error log, it indicate we run our of locks in the SQL server.
The error message looks like this
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration
We need to closely monitor number of the locks being used. If SQL server runs out of locks, the query would fail. Then we need to exam whether SQL server has sufficient memory to let the memory manager to manager the lock or there are query that may use excessive locks . In the later case, we may need to check exam the query plan to optimize the query whenever possible .
How to check the number of locks SQL server is currently using?
How to check the number of locks SQL server is configured?
The run_value/config_value 0 means unlimited . SQL Server would dynamic decide the number of the locks can be used by user. The lock manager would not use more than 60% of available memory to the SQL server.
How to check the lock memory is being used?
DBCC MEMORYSTATUS would also show the the memory usage