Error 1204 - The SQL Server cannot obtain a LOCK resource at this time

[Transcend]

Hi guys

help!! An application is run at 3 in the morning every morning that makes updates to a database. It generates the error

Error 1204
Severity Level 19
Message Text
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.

It can't have run out of locks as the server is configured to unlimited users. And the max locks is something huge (like 20million or so) anyway.

Has anyone had this problem before and how was it resolved??

Transcend
[gorgeous

 

[Inwoner]

Just before running the application, run the following statement to see how many locks there are

select count(*) from master..syslocks

You can save this somewhere to look at the value the next day.

 

[Transcend]

Thanks Inwoner

unfortunately its a mission critical app for an airline that can only be run at 3am every morning. The horror!! I'll give that a try

Transcend

 

[tlbroadbent]

Your application could be the culprit. You may want to escalate the locking from row or page to table as soon as the update activity starts. This requires fewer lock resources and speeds up the process.

Example:
Update MyTable With (tablockx, holdlock)
Set <column list>
Where <criteria> If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Transcend]

Thanks Terry,

I thought that it may be the application, unfortunately its written by a third party. *sigh*
I guess the ball is back in their court

Thanks again

Transcend

 

[moonshadow]

I am looking at an identical problem - 3rd party software even. BOL indicates the other reason for running out of locks may be due to insufficient memory - no matter how high your lock limit it, it will always be restricted by the available memory. The other way to reduce locks is to run the database in single user mode (assuming the 3rd party app only establishes one connection). I can't do this, as my problem occurs during the day when users are updating the system. If I find any solutions, I'll post it here.