Server Cannot obtain a LOCK ressource at this time...

Comments

• Options

  chris.buckingham Posts: 59

  April 9, 2008 12:06PM

  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.

  Chris Buckingham
  Red-Gate support

  0 · Share on Twitter
• Options

  VBourdages Posts: 8

  April 10, 2008 10:27AM

  Thank you for the answer, I will test your recommendation.
  
  
  Great support team !

  0 · Share on Twitter