By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,036 Members | 978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,036 IT Pros & Developers. It's quick & easy.

Why I can't drop a table

P: n/a
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.
Dec 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"Scarab" <yw@lucent.com> 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.

Dec 12 '05 #2

P: n/a
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" <yw@lucent.com> 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.


Dec 12 '05 #3

P: n/a
MC
Try with sp_who2 stored procedure. Or use current activity in the EM.
MC
"Scarab" <yw@lucent.com> 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" <yw@lucent.com> 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.
>
>



Dec 12 '05 #4

P: n/a
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, 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
Dec 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.