Hi all,

Our Corporate DBA decided to make a change on a database that has a ton of
transactions, and used sp_config to set the locks to 10000.
This caused major problems, with processes unable to obtain lock resources.
I'm not that familiar with this setting, and after reading a bit about it,
realized that he had set the maximum number of locks, which was way too low.
I, thinking quickly, but not too clearly, reset the max to the default,
2147483647. I believe what I should have done is set it to 0, to let SQL
server dynamically manage this. I, in an attempt to get back to the
defaults, ran this:
sp_configure 'locks', 0
and restarted SQL

Instead of seeing this, as I expected:

Name Minimum Maximum config_value run_value
locks 5000 2147483647 0 0

I'm now seeing this; it looks like the run_value did not change back to 0
Name Minimum Maximum config_value run_value
locks 5000 2147483647 0 2147483647

I'm running Idera SQL diagnostic manager, and it's now reporting that 410.6
MB of SQL server's 1644MB of RAM is now dedicated to Lock Memory, and is
static. Anybody experienced this before, and if so, what to do?

Thanks,
Roger

MS Sql Server LazyDBA home page