SQL Server has run out of locks

[liupinghua]

I keep getting error msg 1204: SQL Server has run out locks. I tried to increase the locks to 500,000 from the default 5000. It dosen't fix the problem. I captured the tracer and found some queries that always occur when error 1204 occurs. Could poor writtened queries cause sql server running out locks? They could hold the locks for a long time therefore blocking other queries form executing, but can they consume large number of locks? The duration for these queries is about 400 to 1100, which to me means less than a second?

Please advise.

Thank you inadvance!

 

[tlbroadbent]

Which version of SQL Server are you running? Did you restart SQL Server after increasing LOCKS? Restart is required to use the new value. Terry

http://members.home.net/tlbroadbent/index.htm

 

[foxdev]

I've encountered this problem, but rarely (SQLS 7). Usually, SQLS escalates locks (row, then page, then table) as the number of locks increases in an effort to use the most efficient locking scheme.

You are right, liupinghua, that poorly-designed set of queries or other operations can cause this problem. Once you identify the query or sets of operations that are causing this, we can explore better ways of doing the same thing. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[Guest_imported]

It's SQL7 and yes i did restart the server so the change to the number of locks can take effect.

I made index changes and added nolock hint to those queries found. After monitoring the SQL activities the past week, seems"out of locks" error is gone.

Thank you all for the input!

liu