Posted by Erland Sommarskog on 10/01/26 11:34

Scarab (yw@lucent.com) writes:
> When I drop a table in Sqlserver 2000 database, The following error
> occurs:
>
> Server: Msg 1204, Level 19, State 1, Line 1
> The SQL Server cannot obtain a LOCK resource at this time. Rerun your
> statement when there are fewer active users or ask the system
> administrator to check the SQL Server lock and memory configuration.
>
>
> What's wrong? Any help is greatly appreciated, thanks.

Now, that's an interesting error for dropping of table. In difference to
what Greg said, there does not have no be someone else who accessing
that precise table. Instead, SQL Server has run out of locks. Which is
a very unusual thing to happen, as the number of locks normally is
dynamically configured.

Run this to see the current locks configuration:

sp_configure locks

A 0 value in config_value means that SQL Server allocates locks as needed.
A non-zero value means a hard limit.

A brutal way out would be to restart SQL Server. Unless the situation
has resolved by itself by now.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]