Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ggeorge
Starting Member
2 Posts |
 Posted - 2008-02-18 : 15:34:12
|
| Hi All,I am frequently getting an error of Unable to acquire a LOCK at this time. Something about too many users logged in. What is the limit of LOCKS in MS SQL 2005 ? When I switch the cluster nodes, the LOCK issue goes away. The issue only occurs when someone tries a DDL command. Advice. |
|
|
ggeorge
Starting Member
2 Posts |
Posted - 2008-02-18 : 15:43:14
|
| The exact error message is :Event Type: ErrorEvent Source: MSSQLSERVEREvent Category: (2)Event ID: 1204Description:The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. |
 |
|
|
CShaw
Yak Posting Veteran
65 Posts |
Posted - 2008-02-18 : 20:08:12
|
| Are your servers identical in memory, Can you give us more information about the configuration of your servers?Chris Shawwww.SQLonCall.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-18 : 20:36:16
|
| When you switch nodes, the SQL service is restarting which means you've got a new set of memory on the new node. I'd bet that if you just restarted the service (cluster-speak: took offline, brought online) on the current node, the problem would go away too. I seriously doubt that the problem is specific to one node, but rather the problem is with the lack of hardware resources on the current node.How big is your database? How much memory do you have on each node? How many concurrent users are there?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-18 : 20:37:32
|
| Check blocking on the server, looks like there was long running process that locked table. When you do failover, you restart sql service that terminates all processes and releases locks. |
|
|
|
|
|
|
LOCKS issue on SQL Server Cluster