3

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 <X64> (Build 9600: ) (Hypervisor)

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

Improve this question
2
  • What is the version of SQL server ? – KASQLDBA Jan 21 '16 at 9:09
  • Microsoft SQL Server 2014 - 12.0.4213.0 (X64) Jun 9 2015 12:06:16 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) – stackflow532 Jan 21 '16 at 9:53
3

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

Improve this answer
5
  • thanks for your feedback, but i have one question here currently this exception is raised when there is no user are active so it seem there is some activities internally is going on in sql server which cause threshold of lock to exceeds. Is there a way to identify the problem in detail?. – stackflow532 Jan 21 '16 at 9:14
  • @stackflow532 in the log file you should have something like this: spidXXXs Error: 1204, Severity: 19, State: 4. above or after the error message in the question. – Ahmad Abuhasna Jan 21 '16 at 10:30
  • Yes let me past few log data here (data time is GMT) 1/21/16 12:00 AM spid38s This instance of SQL Server has been using a process ID of 1364 since 11/15/2015 6:41:14 AM (local) 11/15/2015 6:41:14 AM (UTC). This is an informational message only; no user action is required. 1/21/16 4:57 AM spid38s Error: 1204, Severity: 19, State: 4. 1/21/16 4:57 AM spid38s same msg above – stackflow532 Jan 21 '16 at 10:39
  • The error is after the msg spid38s This instance of SQL Server has been...... spid38s Error: 1204, Severity: 19, State: 4. – stackflow532 Jan 21 '16 at 10:54
  • @stackflow532 you can check if there is any sql service and find out if there is a 3rd party process causing the issue using Get-Process "sqlservr" and search for user with spid=38 – Ahmad Abuhasna Jan 21 '16 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.