Help! production memory issues??

We just started having some issues with our productin
server. We are runnning SQL2k sp3 on Win2k. We have 3 gigs
of memory and after checking the process, we are currently
only running at 1.9 gig.
The applications connecting to the database seem to be ok,
but we are unable to run any jobs via sql agent. We've
tried stopping/starting the agent and flushing the cache..
Were now going to try and reboot the server.
Here's the error messages were getting, any help would be
greatly appreciated. At this point, we can't even monitor
with Profiler because it says it dosen't have enough
memory to run. I just want to figure out the cause and
prevent it from happening again..
Please help
WARNING: Failed to reserve contiguous memory of Size= 131072.
Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
Available=111911
Global Memory Objects: Resource=1981 Locks=168
SQLCache=77 Replication=2
LockBytes=2 ServerGlobal=45
Xact=200
Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
OS Committed=1617
OS In Use=1611
Query Plan=5575 Optimizer=0
General=2391
Utilities=24 Connection=54120
Procedure Cache: TotalProcs=925 TotalPages=5550
InUsePages=3582
Buffer Counts: Commited=208688 Target=208688 Hashed=143148
InternalReservation=645
ExternalReservation=0 Min Free=508
Buffer Distribution: Stolen=55984 Free=4006
Procedures=5550
Inram=0 Dirty=5718 Kept=0
I/O=1, Latched=133, Other=137296Seems like you've configured SQL Server to use a *lot* of memory, more than
available. Can you post the output from:
sp_configure
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"dba4theday@.yahoo.com" <anonymous@.discussions.microsoft.com> wrote in
message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
> We just started having some issues with our productin
> server. We are runnning SQL2k sp3 on Win2k. We have 3 gigs
> of memory and after checking the process, we are currently
> only running at 1.9 gig.
> The applications connecting to the database seem to be ok,
> but we are unable to run any jobs via sql agent. We've
> tried stopping/starting the agent and flushing the cache..
> Were now going to try and reboot the server.
> Here's the error messages were getting, any help would be
> greatly appreciated. At this point, we can't even monitor
> with Profiler because it says it dosen't have enough
> memory to run. I just want to figure out the cause and
> prevent it from happening again..
> Please help
> WARNING: Failed to reserve contiguous memory of Size=> 131072.
> Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
> Available=111911
> Global Memory Objects: Resource=1981 Locks=168
> SQLCache=77 Replication=2
> LockBytes=2 ServerGlobal=45
> Xact=200
> Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
> OS Committed=1617
> OS In Use=1611
> Query Plan=5575 Optimizer=0
> General=2391
> Utilities=24 Connection=54120
> Procedure Cache: TotalProcs=925 TotalPages=5550
> InUsePages=3582
> Buffer Counts: Commited=208688 Target=208688 Hashed=143148
> InternalReservation=645
> ExternalReservation=0 Min Free=508
> Buffer Distribution: Stolen=55984 Free=4006
> Procedures=5550
> Inram=0 Dirty=5718 Kept=0
> I/O=1, Latched=133, Other=137296|||name minimum
maximum config_value run_value
-- -- --
-- --
affinity mask -2147483648
2147483647 0 0
allow updates 0
1 0 0
awe enabled 0
1 0 0
c2 audit mode 0
1 0 0
cost threshold for parallelism 0
32767 5 5
Cross DB Ownership Chaining 0
1 1 1
cursor threshold -1
2147483647 -1 -1
default full-text language 0
2147483647 1033 1033
default language 0
9999 0 0
fill factor (%) 0
100 0 0
index create memory (KB) 704
2147483647 0 0
lightweight pooling 0
1 0 0
locks 5000
2147483647 0 0
max degree of parallelism 0
32 0 0
max server memory (MB) 4
2147483647 2147483647 2147483647
max text repl size (B) 0
2147483647 65536 65536
max worker threads 32
32767 255 255
media retention 0
365 0 0
min memory per query (KB) 512
2147483647 1024 1024
min server memory (MB) 0
2147483647 0 0
nested triggers 0
1 1 1
network packet size (B) 512
65536 4096 4096
open objects 0
2147483647 0 0
priority boost 0
1 0 0
query governor cost limit 0
2147483647 0 0
query wait (s) -1
2147483647 -1 -1
recovery interval (min) 0
32767 0 0
remote access 0
1 1 1
remote login timeout (s) 0
2147483647 20 20
remote proc trans 0
1 0 0
remote query timeout (s) 0
2147483647 600 600
scan for startup procs 0
1 1 1
set working set size 0
1 0 0
show advanced options 0
1 1 1
two digit year cutoff 1753
9999 2049 2049
user connections 0
32767 0 0
user options 0
32767 0 0
>--Original Message--
>Seems like you've configured SQL Server to use a *lot* of
memory, more than
>available. Can you post the output from:
>sp_configure
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"dba4theday@.yahoo.com"
<anonymous@.discussions.microsoft.com> wrote in
>message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
>> We just started having some issues with our productin
>> server. We are runnning SQL2k sp3 on Win2k. We have 3
gigs
>> of memory and after checking the process, we are
currently
>> only running at 1.9 gig.
>> The applications connecting to the database seem to be
ok,
>> but we are unable to run any jobs via sql agent. We've
>> tried stopping/starting the agent and flushing the
cache..
>> Were now going to try and reboot the server.
>> Here's the error messages were getting, any help would
be
>> greatly appreciated. At this point, we can't even
monitor
>> with Profiler because it says it dosen't have enough
>> memory to run. I just want to figure out the cause and
>> prevent it from happening again..
>> Please help
>> WARNING: Failed to reserve contiguous memory of Size=>> 131072.
>> Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
>> Available=111911
>> Global Memory Objects: Resource=1981 Locks=168
>> SQLCache=77 Replication=2
>> LockBytes=2 ServerGlobal=45
>> Xact=200
>> Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
>> OS Committed=1617
>> OS In Use=1611
>> Query Plan=5575 Optimizer=0
>> General=2391
>> Utilities=24 Connection=54120
>> Procedure Cache: TotalProcs=925 TotalPages=5550
>> InUsePages=3582
>> Buffer Counts: Commited=208688 Target=208688
Hashed=143148
>> InternalReservation=645
>> ExternalReservation=0 Min Free=508
>> Buffer Distribution: Stolen=55984 Free=4006
>> Procedures=5550
>> Inram=0 Dirty=5718 Kept=0
>> I/O=1, Latched=133, Other=137296
>
>.
>|||Nothing strange there. Are you sure there isn't anything else that is using
all the memory? Anything in the agent log?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:032201c3bf5a$504b7470$a301280a@.phx.gbl...
> name minimum
> maximum config_value run_value
> -- -- --
> -- --
> affinity mask -2147483648
> 2147483647 0 0
> allow updates 0
> 1 0 0
> awe enabled 0
> 1 0 0
> c2 audit mode 0
> 1 0 0
> cost threshold for parallelism 0
> 32767 5 5
> Cross DB Ownership Chaining 0
> 1 1 1
> cursor threshold -1
> 2147483647 -1 -1
> default full-text language 0
> 2147483647 1033 1033
> default language 0
> 9999 0 0
> fill factor (%) 0
> 100 0 0
> index create memory (KB) 704
> 2147483647 0 0
> lightweight pooling 0
> 1 0 0
> locks 5000
> 2147483647 0 0
> max degree of parallelism 0
> 32 0 0
> max server memory (MB) 4
> 2147483647 2147483647 2147483647
> max text repl size (B) 0
> 2147483647 65536 65536
> max worker threads 32
> 32767 255 255
> media retention 0
> 365 0 0
> min memory per query (KB) 512
> 2147483647 1024 1024
> min server memory (MB) 0
> 2147483647 0 0
> nested triggers 0
> 1 1 1
> network packet size (B) 512
> 65536 4096 4096
> open objects 0
> 2147483647 0 0
> priority boost 0
> 1 0 0
> query governor cost limit 0
> 2147483647 0 0
> query wait (s) -1
> 2147483647 -1 -1
> recovery interval (min) 0
> 32767 0 0
> remote access 0
> 1 1 1
> remote login timeout (s) 0
> 2147483647 20 20
> remote proc trans 0
> 1 0 0
> remote query timeout (s) 0
> 2147483647 600 600
> scan for startup procs 0
> 1 1 1
> set working set size 0
> 1 0 0
> show advanced options 0
> 1 1 1
> two digit year cutoff 1753
> 9999 2049 2049
> user connections 0
> 32767 0 0
> user options 0
> 32767 0 0
>
> >--Original Message--
> >Seems like you've configured SQL Server to use a *lot* of
> memory, more than
> >available. Can you post the output from:
> >sp_configure
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"dba4theday@.yahoo.com"
> <anonymous@.discussions.microsoft.com> wrote in
> >message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
> >> We just started having some issues with our productin
> >> server. We are runnning SQL2k sp3 on Win2k. We have 3
> gigs
> >> of memory and after checking the process, we are
> currently
> >> only running at 1.9 gig.
> >>
> >> The applications connecting to the database seem to be
> ok,
> >> but we are unable to run any jobs via sql agent. We've
> >> tried stopping/starting the agent and flushing the
> cache..
> >> Were now going to try and reboot the server.
> >> Here's the error messages were getting, any help would
> be
> >> greatly appreciated. At this point, we can't even
> monitor
> >> with Profiler because it says it dosen't have enough
> >> memory to run. I just want to figure out the cause and
> >> prevent it from happening again..
> >>
> >> Please help
> >>
> >> WARNING: Failed to reserve contiguous memory of Size=> >> 131072.
> >> Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
> >> Available=111911
> >> Global Memory Objects: Resource=1981 Locks=168
> >> SQLCache=77 Replication=2
> >> LockBytes=2 ServerGlobal=45
> >> Xact=200
> >> Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
> >> OS Committed=1617
> >> OS In Use=1611
> >> Query Plan=5575 Optimizer=0
> >> General=2391
> >> Utilities=24 Connection=54120
> >> Procedure Cache: TotalProcs=925 TotalPages=5550
> >> InUsePages=3582
> >> Buffer Counts: Commited=208688 Target=208688
> Hashed=143148
> >> InternalReservation=645
> >> ExternalReservation=0 Min Free=508
> >> Buffer Distribution: Stolen=55984 Free=4006
> >> Procedures=5550
> >> Inram=0 Dirty=5718 Kept=0
> >> I/O=1, Latched=133, Other=137296
> >
> >
> >.
> >|||Well after further investigation we found that there were
100's of spid dated back to November coming in via SQL DMO.
Those connections were still left open and we believe they
have been the cause as the job calling the sp_oa proc's
started the same time we started seeing the error messages.
It states that there was an issue with those type of
proc's but it was fixed in SP1, were now on SP3...
The server has been running fine since we rebooted and if
we see this issue again, we are going to try and kill the
spid prior to the reboot to see if that fixes the problem.
Do you have any other idea's?
>--Original Message--
>Nothing strange there. Are you sure there isn't anything
else that is using
>all the memory? Anything in the agent log?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:032201c3bf5a$504b7470$a301280a@.phx.gbl...
>> name minimum
>> maximum config_value run_value
>> -- -- --
--
>> -- --
>> affinity mask -2147483648
>> 2147483647 0 0
>> allow updates 0
>> 1 0 0
>> awe enabled 0
>> 1 0 0
>> c2 audit mode 0
>> 1 0 0
>> cost threshold for parallelism 0
>> 32767 5 5
>> Cross DB Ownership Chaining 0
>> 1 1 1
>> cursor threshold -1
>> 2147483647 -1 -1
>> default full-text language 0
>> 2147483647 1033 1033
>> default language 0
>> 9999 0 0
>> fill factor (%) 0
>> 100 0 0
>> index create memory (KB) 704
>> 2147483647 0 0
>> lightweight pooling 0
>> 1 0 0
>> locks 5000
>> 2147483647 0 0
>> max degree of parallelism 0
>> 32 0 0
>> max server memory (MB) 4
>> 2147483647 2147483647 2147483647
>> max text repl size (B) 0
>> 2147483647 65536 65536
>> max worker threads 32
>> 32767 255 255
>> media retention 0
>> 365 0 0
>> min memory per query (KB) 512
>> 2147483647 1024 1024
>> min server memory (MB) 0
>> 2147483647 0 0
>> nested triggers 0
>> 1 1 1
>> network packet size (B) 512
>> 65536 4096 4096
>> open objects 0
>> 2147483647 0 0
>> priority boost 0
>> 1 0 0
>> query governor cost limit 0
>> 2147483647 0 0
>> query wait (s) -1
>> 2147483647 -1 -1
>> recovery interval (min) 0
>> 32767 0 0
>> remote access 0
>> 1 1 1
>> remote login timeout (s) 0
>> 2147483647 20 20
>> remote proc trans 0
>> 1 0 0
>> remote query timeout (s) 0
>> 2147483647 600 600
>> scan for startup procs 0
>> 1 1 1
>> set working set size 0
>> 1 0 0
>> show advanced options 0
>> 1 1 1
>> two digit year cutoff 1753
>> 9999 2049 2049
>> user connections 0
>> 32767 0 0
>> user options 0
>> 32767 0 0
>>
>> >--Original Message--
>> >Seems like you've configured SQL Server to use a *lot*
of
>> memory, more than
>> >available. Can you post the output from:
>> >sp_configure
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"dba4theday@.yahoo.com"
>> <anonymous@.discussions.microsoft.com> wrote in
>> >message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
>> >> We just started having some issues with our productin
>> >> server. We are runnning SQL2k sp3 on Win2k. We have 3
>> gigs
>> >> of memory and after checking the process, we are
>> currently
>> >> only running at 1.9 gig.
>> >>
>> >> The applications connecting to the database seem to
be
>> ok,
>> >> but we are unable to run any jobs via sql agent.
We've
>> >> tried stopping/starting the agent and flushing the
>> cache..
>> >> Were now going to try and reboot the server.
>> >> Here's the error messages were getting, any help
would
>> be
>> >> greatly appreciated. At this point, we can't even
>> monitor
>> >> with Profiler because it says it dosen't have enough
>> >> memory to run. I just want to figure out the cause
and
>> >> prevent it from happening again..
>> >>
>> >> Please help
>> >>
>> >> WARNING: Failed to reserve contiguous memory of
Size=>> >> 131072.
>> >> Query Memory Manager: Grants=0 Waiting=0
Maximum=111911
>> >> Available=111911
>> >> Global Memory Objects: Resource=1981 Locks=168
>> >> SQLCache=77 Replication=2
>> >> LockBytes=2 ServerGlobal=45
>> >> Xact=200
>> >> Dynamic Memory Manager: Stolen=61534 OS
Reserved=1656
>> >> OS Committed=1617
>> >> OS In Use=1611
>> >> Query Plan=5575 Optimizer=0
>> >> General=2391
>> >> Utilities=24 Connection=54120
>> >> Procedure Cache: TotalProcs=925 TotalPages=5550
>> >> InUsePages=3582
>> >> Buffer Counts: Commited=208688 Target=208688
>> Hashed=143148
>> >> InternalReservation=645
>> >> ExternalReservation=0 Min Free=508
>> >> Buffer Distribution: Stolen=55984 Free=4006
>> >> Procedures=5550
>> >> Inram=0 Dirty=5718 Kept=0
>> >> I/O=1, Latched=133, Other=137296
>> >
>> >
>> >.
>> >
>
>.
>|||I think there's a good chance that you've found the problem, I can very well
imagine a memory leak related to sp_OA etc.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:0ba601c3bf6a$62e77380$a001280a@.phx.gbl...
> Well after further investigation we found that there were
> 100's of spid dated back to November coming in via SQL DMO.
> Those connections were still left open and we believe they
> have been the cause as the job calling the sp_oa proc's
> started the same time we started seeing the error messages.
> It states that there was an issue with those type of
> proc's but it was fixed in SP1, were now on SP3...
> The server has been running fine since we rebooted and if
> we see this issue again, we are going to try and kill the
> spid prior to the reboot to see if that fixes the problem.
> Do you have any other idea's?
> >--Original Message--
> >Nothing strange there. Are you sure there isn't anything
> else that is using
> >all the memory? Anything in the agent log?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:032201c3bf5a$504b7470$a301280a@.phx.gbl...
> >> name minimum
> >> maximum config_value run_value
> >> -- -- --
> --
> >> -- --
> >> affinity mask -2147483648
> >> 2147483647 0 0
> >> allow updates 0
> >> 1 0 0
> >> awe enabled 0
> >> 1 0 0
> >> c2 audit mode 0
> >> 1 0 0
> >> cost threshold for parallelism 0
> >> 32767 5 5
> >> Cross DB Ownership Chaining 0
> >> 1 1 1
> >> cursor threshold -1
> >> 2147483647 -1 -1
> >> default full-text language 0
> >> 2147483647 1033 1033
> >> default language 0
> >> 9999 0 0
> >> fill factor (%) 0
> >> 100 0 0
> >> index create memory (KB) 704
> >> 2147483647 0 0
> >> lightweight pooling 0
> >> 1 0 0
> >> locks 5000
> >> 2147483647 0 0
> >> max degree of parallelism 0
> >> 32 0 0
> >> max server memory (MB) 4
> >> 2147483647 2147483647 2147483647
> >> max text repl size (B) 0
> >> 2147483647 65536 65536
> >> max worker threads 32
> >> 32767 255 255
> >> media retention 0
> >> 365 0 0
> >> min memory per query (KB) 512
> >> 2147483647 1024 1024
> >> min server memory (MB) 0
> >> 2147483647 0 0
> >> nested triggers 0
> >> 1 1 1
> >> network packet size (B) 512
> >> 65536 4096 4096
> >> open objects 0
> >> 2147483647 0 0
> >> priority boost 0
> >> 1 0 0
> >> query governor cost limit 0
> >> 2147483647 0 0
> >> query wait (s) -1
> >> 2147483647 -1 -1
> >> recovery interval (min) 0
> >> 32767 0 0
> >> remote access 0
> >> 1 1 1
> >> remote login timeout (s) 0
> >> 2147483647 20 20
> >> remote proc trans 0
> >> 1 0 0
> >> remote query timeout (s) 0
> >> 2147483647 600 600
> >> scan for startup procs 0
> >> 1 1 1
> >> set working set size 0
> >> 1 0 0
> >> show advanced options 0
> >> 1 1 1
> >> two digit year cutoff 1753
> >> 9999 2049 2049
> >> user connections 0
> >> 32767 0 0
> >> user options 0
> >> 32767 0 0
> >>
> >>
> >> >--Original Message--
> >> >Seems like you've configured SQL Server to use a *lot*
> of
> >> memory, more than
> >> >available. Can you post the output from:
> >> >sp_configure
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at:
> >> >http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"dba4theday@.yahoo.com"
> >> <anonymous@.discussions.microsoft.com> wrote in
> >> >message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
> >> >> We just started having some issues with our productin
> >> >> server. We are runnning SQL2k sp3 on Win2k. We have 3
> >> gigs
> >> >> of memory and after checking the process, we are
> >> currently
> >> >> only running at 1.9 gig.
> >> >>
> >> >> The applications connecting to the database seem to
> be
> >> ok,
> >> >> but we are unable to run any jobs via sql agent.
> We've
> >> >> tried stopping/starting the agent and flushing the
> >> cache..
> >> >> Were now going to try and reboot the server.
> >> >> Here's the error messages were getting, any help
> would
> >> be
> >> >> greatly appreciated. At this point, we can't even
> >> monitor
> >> >> with Profiler because it says it dosen't have enough
> >> >> memory to run. I just want to figure out the cause
> and
> >> >> prevent it from happening again..
> >> >>
> >> >> Please help
> >> >>
> >> >> WARNING: Failed to reserve contiguous memory of
> Size=> >> >> 131072.
> >> >> Query Memory Manager: Grants=0 Waiting=0
> Maximum=111911
> >> >> Available=111911
> >> >> Global Memory Objects: Resource=1981 Locks=168
> >> >> SQLCache=77 Replication=2
> >> >> LockBytes=2 ServerGlobal=45
> >> >> Xact=200
> >> >> Dynamic Memory Manager: Stolen=61534 OS
> Reserved=1656
> >> >> OS Committed=1617
> >> >> OS In Use=1611
> >> >> Query Plan=5575 Optimizer=0
> >> >> General=2391
> >> >> Utilities=24 Connection=54120
> >> >> Procedure Cache: TotalProcs=925 TotalPages=5550
> >> >> InUsePages=3582
> >> >> Buffer Counts: Commited=208688 Target=208688
> >> Hashed=143148
> >> >> InternalReservation=645
> >> >> ExternalReservation=0 Min Free=508
> >> >> Buffer Distribution: Stolen=55984 Free=4006
> >> >> Procedures=5550
> >> >> Inram=0 Dirty=5718 Kept=0
> >> >> I/O=1, Latched=133, Other=137296
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >