Lock Issue

I've got a 2000 server (2G usable RAM) where we just
installed SP2. Immediately after installation I began
getting the below error:
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.
Error: 1204, Severity: 19, State: 1
I've got the default configuration setting for locks of 0
and nothing has changed on this server that I know except
for SP2. Furthermore, I've been monitoring the total
number of locks at any given time and I'm not seeing
anything that should cause problems. The most locks I've
seen is about 40,000.
The last time we got this error msg in the error log, I
had two index defrag jobs going and there was bulk load
and/or delete job running.
Is this simply memory related? Is there anything with SP2
that might cause this (especially if it uses extra memory
for index defrags)?
Any insight would be much appreciated?!Hi Curt.
What is your configuration for locks? Run this to find out:
exec sp_configure 'locks'
Perhaps it got set to 40000 maximum during SP2 installation somehow?
Regards,
Greg Linwood
SQL Server MVP
"CurtM" <cndmoyer@.hotmail.com> wrote in message
news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
> I've got a 2000 server (2G usable RAM) where we just
> installed SP2. Immediately after installation I began
> getting the below error:
> 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.
> Error: 1204, Severity: 19, State: 1
> I've got the default configuration setting for locks of 0
> and nothing has changed on this server that I know except
> for SP2. Furthermore, I've been monitoring the total
> number of locks at any given time and I'm not seeing
> anything that should cause problems. The most locks I've
> seen is about 40,000.
> The last time we got this error msg in the error log, I
> had two index defrag jobs going and there was bulk load
> and/or delete job running.
> Is this simply memory related? Is there anything with SP2
> that might cause this (especially if it uses extra memory
> for index defrags)?
> Any insight would be much appreciated?!
>|||It is set to 0 for run and config value (which I am sure
it was pre-SP2 as well).
>--Original Message--
>Hi Curt.
>What is your configuration for locks? Run this to find
out:
>exec sp_configure 'locks'
>Perhaps it got set to 40000 maximum during SP2
installation somehow?
>Regards,
>Greg Linwood
>SQL Server MVP
>"CurtM" <cndmoyer@.hotmail.com> wrote in message
>news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
>> I've got a 2000 server (2G usable RAM) where we just
>> installed SP2. Immediately after installation I began
>> getting the below error:
>> 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.
>> Error: 1204, Severity: 19, State: 1
>> I've got the default configuration setting for locks of
0
>> and nothing has changed on this server that I know
except
>> for SP2. Furthermore, I've been monitoring the total
>> number of locks at any given time and I'm not seeing
>> anything that should cause problems. The most locks
I've
>> seen is about 40,000.
>> The last time we got this error msg in the error log, I
>> had two index defrag jobs going and there was bulk load
>> and/or delete job running.
>> Is this simply memory related? Is there anything with
SP2
>> that might cause this (especially if it uses extra
memory
>> for index defrags)?
>> Any insight would be much appreciated?!
>
>.
>|||At this point it is just memory issue and you may have a lot of low
granularity locks such as RID or Key. You may want to escalate if possible
using hints for the problematic query
"CurtM" <cndmoyer@.hotmail.com> wrote in message
news:08c201c391c9$77b35000$a301280a@.phx.gbl...
> It is set to 0 for run and config value (which I am sure
> it was pre-SP2 as well).
> >--Original Message--
> >Hi Curt.
> >
> >What is your configuration for locks? Run this to find
> out:
> >
> >exec sp_configure 'locks'
> >
> >Perhaps it got set to 40000 maximum during SP2
> installation somehow?
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"CurtM" <cndmoyer@.hotmail.com> wrote in message
> >news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
> >> I've got a 2000 server (2G usable RAM) where we just
> >> installed SP2. Immediately after installation I began
> >> getting the below error:
> >>
> >> 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.
> >>
> >> Error: 1204, Severity: 19, State: 1
> >>
> >> I've got the default configuration setting for locks of
> 0
> >> and nothing has changed on this server that I know
> except
> >> for SP2. Furthermore, I've been monitoring the total
> >> number of locks at any given time and I'm not seeing
> >> anything that should cause problems. The most locks
> I've
> >> seen is about 40,000.
> >>
> >> The last time we got this error msg in the error log, I
> >> had two index defrag jobs going and there was bulk load
> >> and/or delete job running.
> >>
> >> Is this simply memory related? Is there anything with
> SP2
> >> that might cause this (especially if it uses extra
> memory
> >> for index defrags)?
> >>
> >> Any insight would be much appreciated?!
> >>
> >
> >
> >.
> >