lock setting ?

[timscronin]

I am getting the message
DESCRIPTION: Error: 1204, Severity: 19, State: 1 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 is a 3rd party app I can't change with some ugly sql.
My current lock setting is

min max config run
locks 5000 2147483647 0 0


Do I need to change the min value to higher?

 

[mrdenny]

When you are getting this, how many locks are in use?

0 indicates that SQL will allocate and deallocate lock structures dynamically.

Here is the info from BOL.

Use the locks option to set the maximum number of available locks, thereby limiting the amount of memory the Database Engine uses for them. The default setting is 0, which allows the Database Engine to allocate and deallocate lock structures dynamically, based on changing system requirements.

When the server is started with locks set to 0, the lock manager acquires sufficient memory from the Database Engine for an initial pool of 2,500 lock structures. As the lock pool is exhausted, additional memory is acquired for the pool.

Generally, if more memory is required for the lock pool than is available in the Database Engine memory pool, and more computer memory is available (the max server memory threshold has not been reached), the Database Engine allocates memory dynamically to satisfy the request for locks. However, if allocating that memory would cause paging at the operating system level (for example, if another application is running on the same computer as an instance of SQL Server and using that memory), more lock space is not allocated. The dynamic lock pool does not acquire more than 60 percent of the memory allocated to the Database Engine. After the lock pool has reached 60 percent of the memory acquired by an instance of the Database Engine, or no more memory is available on the computer, further requests for locks generate an error.

Allowing SQL Server to use locks dynamically is the recommended configuration. However, you can set locks and override the ability of SQL Server to allocate lock resources dynamically. When locks is set to a value other than 0, the Database Engine cannot allocate more locks than the value specified in locks. Increase this value if SQL Server displays a message that you have exceeded the number of available locks. Because each lock consumes memory (96 bytes per lock), increasing this value can require increasing the amount of memory dedicated to the server.

The locks option also affects when lock escalation occurs. When locks is set to 0, lock escalation occurs when the memory used by the current lock structures reaches 40 percent of the Database Engine memory pool. When locks is not set to 0, lock escalation occurs when the number of locks reaches 40 percent of the value specified for locks.

The locks option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change locks only when show advanced options is set to 1. The setting takes effect after the server is restarted.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com