Why I can't drop a table

[Scarab]

Hi,
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.

[Greg D. Moore \(Strider\)]

"Scarab" <[e-mail]> wrote in message
news:dn********@netnews.net.lucent.com...

Hi,
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.

Someone else is accessing the table.


What's wrong? Any help is greatly appreciated, thanks.

[Scarab]

Thanks for your reply, how can i find out who is accesing the table? Thanks
"Greg D. Moore (Strider)" <mo****************@greenms.com> wrote in message
news:pl****************@newsread1.news.atl.earthli nk.net...

"Scarab" <[e-mail]> wrote in message
news:dn********@netnews.net.lucent.com...

Hi,
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.

Someone else is accessing the table.

What's wrong? Any help is greatly appreciated, thanks.

[MC]

Try with sp_who2 stored procedure. Or use current activity in the EM.
MC
"Scarab" <[e-mail]> wrote in message
news:dn********@netnews.net.lucent.com...

Thanks for your reply, how can i find out who is accesing the table?
Thanks
"Greg D. Moore (Strider)" <mo****************@greenms.com> wrote in
message
news:pl****************@newsread1.news.atl.earthli nk.net...

"Scarab" <[e-mail]> wrote in message
news:dn********@netnews.net.lucent.com...

> Hi,
> 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.
>

Someone else is accessing the table.

>
> What's wrong? Any help is greatly appreciated, thanks.
>
>

[Erland Sommarskog]

Scarab ([e-mail]) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx