What does Error: 1204, Severity: 19, State: 4 mean? I've found this error in our SQL log:


  "The instance of the SQL Server Database Engine 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. Error: 1204, Severity: 19, State: 4."


From last 5 days this exception is coming in our staging SQL Server. There is no other process running still it is publishing error every night.

Select @@Version

Microsoft SQL Server 2014 - 12.0.4213.0 (X64) Jun 9 2015 12:06:16 Copyright 
Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 6.3 &lt;X64&gt; (Build 9600: ) (Hypervisor)


Does anyone know how to fix this? How to diagnose such error and find who is real culprit for it.
Explanation:

This error occurs when there are not enough system locks to complete the current command. SQL Server then attempts to obtain a LOCK block to represent and control the desired lock. When dynamically configured, the lock limit is determined by the available memory. When statically configured, the lock limit is determined by the sp_configure setting.

Action:

you have two options earthier wait for sometime and re-execute the same command(s), or reconfigure locks value by executing the following commands

sp_configure locks, XXXXXX     where XXXXXX value higher than the original one
GO
RECONFIGURE WITH OVERRIDE
GO


Regarding troulbeshooting this error please refer to the log and execute the following command:

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name';


This will show you the needed info, and you can check Activity Monitor too

some useful links:
http://www.sqlservercentral.com/Forums/Topic141154-92-1.aspx
https://technet.microsoft.com/en-us/library/aa258769(v=sql.80).aspx