Monday, October 28, 2013

1 SQL Server error 1204

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

image

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'

image

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'

image

DBCC MEMORYSTATUS would also show the the memory usage

 
DBCC MEMORYSTATUS

image

Reference

http://technet.microsoft.com/en-us/library/aa337440(v=sql.105).aspx

1 comments:

  1. 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

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates