Need to increase LOCKS

[babeo]

Hi,
I get errror messages of
"Error : 1204, Severity: 19, State: 3
SQL Server has run out of LOCKS. Re-run your command when there are fewer active users, or ask your System Administrator to reconfigure SQL Server with more LOCKS. "

What does it mean? Does it mean that I need to increase the connection for users? (which is set up at: Right click on the Server, select "Configure...", and the choose the tab "configuration", and change the option "users connection" to more than the current set up, eg current is 20, now set it to eg 50) or is it something else?

Also, in this case, it seems the connection with the database is not there any more, should I reboot the server or recycle the database only?

Please help
Thanks.

 

[tlbroadbent]

Locks and connections are two different things. Increasing the number of connections will not increase the number of available locks.

The correct answer to your problem depends on the version of SQL Server you are running. Execute sp_configure to see how many locks are configured on your server.

exec sp_configure 'locks'

SQL Server 7 and higher dynamically allocates locks. The default setting is 0 (zero) which allows SQL Server to handle lock allocation.

You may need to add lock hints to queries. For example, you can add the tablock hint to a table that is being updated. This will force SQL Server to lock the table immediately and not lock rows or pages which will require greater lock resources. Sometimes, it is advantageous to use the nolock hint in select queries. This will reduce the number of locks required.

See faq183-714 (How can I eliminate or reduce locking?) for help with eliminating or reducing locks. You can also read about locks and SQL Server configuration parameters in SQL Books Online.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[babeo]

Hi Terry,
I have another question.
Although you already stated that "Lock and connection are 2 different things", but I would like to confirm again if there is any related in the case of if the LOCKs is run out, then it will cause "Failed to connect to the database" too?

The reason I ask because immediately right after the message of running out of lock, the next message is failed to connect to the database, which makes me not so sure if they are related together.

Thanks.

 

[tlbroadbent]

Running out of locks may cause a login failure, though I've not seen that myself and can't be sure. Increasing the number of allowable connections will not help with the lock problem. However, reducing the number of locks used or increasing the number of available locks may help with the connection problem.

How much memory does the server have? The number of locks available could be limited by the amount of RAM.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[babeo]

Thanks Terry,

I found the problem.
It's the miss set up of IP address in "Client Configuration Utility" that has the old IP when we switch the server to the new IP.
And I think because all processes can not connectd to the database, they all retry to connect (?) again and again, and which causes running out of locks. It's just my thought.