Solved

The SQL Server cannot obtain a LOCK resource at this time

Posted on 2004-08-17
4
4,505 Views
Last Modified: 2012-06-21
Can someone help explain this error I am getting:

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

And how I can fix it :)
0
Comment
Question by:objects
  • 2
4 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11827037
From SQL Server books online:

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.

If you continue to encounter this problem, make sure your statistics are up to date, you have sufficient indexes to run your query efficiently, and that the transaction isolation level for your application is not more restrictive than necessary.

Action
Either execute the command again when activity on the server is low, or have the system administrator increase the number of locks by executing sp_configure from the master database.

To view the current configuration:

sp_configure locks
GO

This reports the minimum, maximum, current run, and configuration values. To increase the number of locks, run sp_configure again, specifying the number of locks to be configured. For example, to configure 10,000 locks:

sp_configure locks, 10000
GO
RECONFIGURE WITH OVERRIDE
GO

Stop and restart Microsoft® SQL Server™ so the changes can take effect. Locks are allocated at system startup.

If the number of locks cannot be increased at the current time, and the single action requires more locks than the server is currently configured for, you may be able to reduce the number of locks required for the operation. For example, try the following:

For large UPDATE statements, break the updates into smaller units that will affect only a subset of records at a time. For example, you could use the primary key, changing the single UPDATE statement from:
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 1000 AND 9999
GO

to several UPDATE statements:

UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 1000 AND 4999
GO
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 5000 AND 9999
GO

For a maintenance type of task or for a global update, consider putting the database into single-user mode (if it is feasible to keep other users out of the database). Single-user mode does not set locks, so you will not run out of locks, and the operation will run somewhat faster (because you save the locking overhead).

For a large bulk copy operation, the entire operation is treated as a single transaction. When you use the batch parameter (-b), the bcp utility will treat the operation in small transactions with the number of rows specified. At the end of each small transaction, the system resources held by that transaction are freed, so fewer locks are needed.
0
 
LVL 92

Author Comment

by:objects
ID: 11827049
Can you translate that for a non-db person :)
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 500 total points
ID: 11827100
I'll try. :)  Relational database systems have a variety of "locking" mechanisms to enforce some forms of data integrity. A simple example is when two people try to update the same data at the same time. There has to be some way to determine which change goes first and second. One way it has to do this is to put a "lock" on a table (or page of data in the table, or a single row) as soon as the first request starts, so that a second or third request to change data isn't honored until the first lock is released.

Such things can lead to problems like the one you're encountering. Another kind is a "deadlock", where two people try to update two tables at the same time: person A tries to update first table A then table B as part of a single "transaction" while person B tries to update first table B, then table A. Person A's request gets a lock on table A while person B's request gets a lock on table B. But person A's request can't finish and release it's lock on table A until it can update table B, and person B's request can't finish and release it's lock on table B until it can update table A. In these circumstances, SQL Server chooses one of the processes to kill and be rolled back.

Your specific problem could have any of several causes, as outlined in BOL. For us to help you troubleshoot it, we need more information, such as:

1. What did you do that returned the error message? If it was running a query or stored procedure, post the code.
2. What is the structure of the tables involved? (table names, column names, and datatypes)
3. How many rows of data are in each of these tables?
4. What indexes exist on these tables? (if any)
5. How much memory does your SQL Server computer have?
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel conversion issue with Sql server 14 50
SQL Query resolving a string conversion issue 26 39
Updating a column using a lookup on another table. 6 20
SQL VIEW 7 23
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now