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

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