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?
select request_session_id, resource_type,db_name(resource_database_id),
resource_associated_entity_id,
request_mode,request_type,
request_status
from sys.dm_tran_locks
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.
select * from sys.configurations
where name ='locks';
go
sp_configure 'locks'
How to check the lock memory is being used?
SELECT object_name,
counter_name,
instance_name,
cntr_value,
cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory Manager%';
select * from sys.dm_os_memory_clerks where type like 'OBJECTSTORE_LOCK_MANAGER'
DBCC MEMORYSTATUS would also show the the memory usage
DBCC MEMORYSTATUS
Reference
http://technet.microsoft.com/en-us/library/aa337440(v=sql.105).aspx
Thanks for sharing this informative article with us. I have found another helpful article on the same. You can read from here: http://sqltechtips.blogspot.com/2016/02/database-can-not-obtain-lock.html
ReplyDelete