About sqlserver2000 error in application log

Hi !
i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled; my
db as work fine but from same day a error was dispaly in my application log
:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 28/07/2006
Time: 9.32.02
User: N/A
Computer: CLSSQL2000
Description:
Error: 1204, Severity: 19, State: 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
I have check for memory free with taskmanager and there is 2 Gb yte frre (i
have awe with 6 Gbyte configured) and with spconfigure but the awe and
memory configuration seems ok.
But why this error occurred ? What happened ?
Thanks in advance!is there any long running store procedure / query running on that server
with following statement.
Select *... INTO <table1> FROM ...
When queries like that runs, it applies exclusive lock on the system tables.
you would get "SQL Server cannot obtain a LOCK resource at this time" error
on the database until the above sp/query is completed. you may get the same
error when you try to list the table in enterprise manager.
change the code by creating the table first and do the insert into, instead
of Select ... into ...
Create <table> ( field1...,
field2...)
Insert into <table> SELECT ... FROM ...
-Saravanan
<io.com@.newsgroup.nospam> wrote in message
news:ezy5pGWxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi !
> i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled; my
> db as work fine but from same day a error was dispaly in my application
> log :
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 28/07/2006
> Time: 9.32.02
> User: N/A
> Computer: CLSSQL2000
> Description:
> Error: 1204, Severity: 19, State: 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
>
> I have check for memory free with taskmanager and there is 2 Gb yte frre
> (i have awe with 6 Gbyte configured) and with spconfigure but the awe and
> memory configuration seems ok.
> But why this error occurred ? What happened ?
> Thanks in advance!
>|||Hi,
Thanks Saravanan for greate suggestion. You can also refer to following
article:
323630 How to resolve blocking problems that are caused by lock escalation
in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;323630
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================
Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: <io.com@.newsgroup.nospam>
>>Subject: About sqlserver2000 error in application log
>>Date: Mon, 21 Aug 2006 22:37:42 +0200
>>Lines: 30
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <ezy5pGWxGHA.5056@.TK2MSFTNGP06.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 62.241.4.149
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP06.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:442635
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Hi !
>>i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled;
my
>>db as work fine but from same day a error was dispaly in my application
log
>>:
>>Event Type: Error
>>Event Source: MSSQLSERVER
>>Event Category: (2)
>>Event ID: 17052
>>Date: 28/07/2006
>>Time: 9.32.02
>>User: N/A
>>Computer: CLSSQL2000
>>Description:
>>Error: 1204, Severity: 19, State: 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
>>
>>I have check for memory free with taskmanager and there is 2 Gb yte frre
(i
>>have awe with 6 Gbyte configured) and with spconfigure but the awe and
>>memory configuration seems ok.
>>But why this error occurred ? What happened ?
>>Thanks in advance!
>>