Hi Meg,

Normally, the DB will escalate to a table lock but some other process must have a lock on your main table(s) so that this is prevented from happening. So - you have 3 easy options:

1: You can trust the dynamic allocation of locks by setting the configuration value to 0. Then up to 40% of your memory can be allocated to locks - or you can just You can increase the number of locks

2: You can arbitrarily pick a larger number of locks based on the memory that your server has and set reconfigure locks to that value.

3: You can look at what is running, what it is doing, guesstimate the number of rows affected and assume that SQL server is trying to do row locks and set the locks to that number + a safety factor.

Currently, locks use 350,000 * 96 bytes / lock = 33,600,000 bytes (32 meg)

Some other options are to avoid the concurrency problems which prevent you from getting a table lock or to re-write the process so that it doesn't need so many locks (for example, if you are doing a massive update or delete, you can use the set row count functionality and then do the update/delete in a loop - the downside is that there is no transactional integrity for the whole transaction.

HTH

. . . Tom Zeblisky


On 11/27/2002 08:55:55 AM Meg Hardy wrote:
> Hi Gurus,
>
> We are having a problem with our nightly load which has just recently
> started occurring. One of the jobs fails with the following error when I
> am sent from an alert.
>
> SQL Server has run out of LOCKS. Rerun your statement when there are fewer
> active users, or ask the system administrator to reconfigure SQL Server with
> more LOCKS
>
> I have checked the sp_configure value for Locks and I notice that ours is
> set to 350000. Can anyone help with how to configure or decide on the best
> value for locks?
>
> Thanks for your help!
> Meg
>
>
> **********************************************************************
> DISCLAIMER
> Any opinions expressed in this email are those of the individual and not
> necessarily of Mondial Assistance (UK) Ltd. This email and any files
> transmitted with it, including replies and forwarded copies (which may contain
> alterations) subsequently transmitted from the Company, are confidential and
> solely for the use of the intended recipient. If you are not the intended
> recipient or the person responsible for delivering to the intended recipient,
> be advised that you have received this email in error and that any use is
> strictly prohibited.
>
> If you have received this email in error please notify the IT support desk by
> telephone on +44 (0)208 2393984 or via email to
> [Email Address Removed] including a copy of this message. Please
> then delete this email and destroy any copies of it. Thank you.
>
> Mondial Assistance (UK) Ltd
> Mondial House
> 102 George Street
> Croydon
> CR9 1AJ




-----------------------------------------------------------------
Visit our Internet site at http://www.reuters.com

Get closer to the financial markets with Reuters Messaging - for more
information and to register, visit http://www.reuters.com/messaging

Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
MS Sql Server LazyDBA home page