BOL advises that altering the default can only constrain the number of locks
available since the default is to take the maximum memory available for
LOCKS.

A circumvention I have used is to put the database into single user for the
duration of the update as locks will not then be used. It was admittedly
embarrassing on the morning when the update had hung overnight!

> -----Original Message-----
> From: Meg Hardy [SMTP:[Email Address Removed] Sent: Wednesday, November 27, 2002 1:56 PM
> To: LazyDBA.com Discussion
> Subject: locks
>
> 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
>
> Mondial Assistance (UK) Ltd Registered in England No.1710361 Registered
> office as above
> **********************************************************************
>
>
> ________________________________________________________________________
> This email has been scanned for all viruses by the MessageLabs SkyScan
> service. For more information on a proactive anti-virus service working
> around the clock, around the globe, visit http://www.messagelabs.com
> ________________________________________________________________________
MS Sql Server LazyDBA home page