"There is insufficient system memory to run this query"

Hi
Within a day of the sql server service being up we're getting memory errors
returned from SQL Server by the applications that use it and query analyser.
I suspect that one of our sp's or jobs is leaking memory. Does anyone know
how i can test which one it is?
Win2k sp4, SQL Server 2000 sp3
Thanks
MattThese articles may help you.
http://www.sql-server-performance.c...nce_audit10.asp
+
http://support.microsoft.com/?id=271624
http://support.microsoft.com/defaul...kb;en-us;316749
http://www.sqlservercentral.com/col...rfinfotable.asp
"Matt" wrote:

> Hi
> Within a day of the sql server service being up we're getting memory error
s
> returned from SQL Server by the applications that use it and query analyse
r.
> I suspect that one of our sp's or jobs is leaking memory. Does anyone kno
w
> how i can test which one it is?
> Win2k sp4, SQL Server 2000 sp3
> Thanks
> Matt
>
>

"Out of memory" error

I get Run-time error '-2147024882 (8007000e) & "Out of menory" errors when I replicate with SQL server 7. I have made a VB program that uses SQLServer7 SP3's replication. Program gets through most of it's subscriptions but in the last one it gives these error messages. Sometimes when I push ok on that message box, replication continues and end with no errors, but sometimes it just shuts down without ending the replication process.

M.U.RMicrosoft talks about a memory leak in the following Technet article, I don't know if this helps?

FIX: Replication Initialize Method Causes Handle Leak on Failure (Q243741) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243741)

Also this article mentions error number -2147024882 (8007000e), I know it's about DTS and Oracle, but at the bottom it makes note that the "Out of Memory" is due to another problem.

PRB: DTS Transfer From SQL Server to Oracle Through OLE DB Provider Shows Ongoing Private Bytes Consumption (Q293320) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q293320)

"Maximum Memory" setting in SQL Server properties

I have inherrited a sql 2k standard server running Win2K standard Ed with 3.5
gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and has
4 CPUs.
The "maximum memory" setting is set to 3500 MB and SQL is using around 1.75
GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any inputs
on if the "maximum memory" setting should be left to 3500mb or should it be
reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
transaction log should not be on a raid 5 for performance reasons and I know
that SQL 2k Standard + Win2k standard will not let me use more than 2 GB of
RAM for SQL. The average Physical Disk (_total) activity is 56% which is
considered very high.
Any inputs would be appreciated on this setting while we order the SQL2K
Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
drives...Hi
SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
will not use it. You need to install SQL Server Expertise Edition to use
more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
area.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> I have inherrited a sql 2k standard server running Win2K standard Ed with
3.5
> gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and
has
> 4 CPUs.
> The "maximum memory" setting is set to 3500 MB and SQL is using around
1.75
> GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any
inputs
> on if the "maximum memory" setting should be left to 3500mb or should it
be
> reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
> transaction log should not be on a raid 5 for performance reasons and I
know
> that SQL 2k Standard + Win2k standard will not let me use more than 2 GB
of
> RAM for SQL. The average Physical Disk (_total) activity is 56% which is
> considered very high.
> Any inputs would be appreciated on this setting while we order the SQL2K
> Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
> drives...
>|||Thanks for your reply.
I fully understand what you are saying (as it was also statement in my
request). I am interested to know about he virtual memory portion of it.
Thanks again
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
> will not use it. You need to install SQL Server Expertise Edition to use
> more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
> leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
> area.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> > I have inherrited a sql 2k standard server running Win2K standard Ed with
> 3.5
> > gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and
> has
> > 4 CPUs.
> >
> > The "maximum memory" setting is set to 3500 MB and SQL is using around
> 1.75
> > GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any
> inputs
> > on if the "maximum memory" setting should be left to 3500mb or should it
> be
> > reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
> > transaction log should not be on a raid 5 for performance reasons and I
> know
> > that SQL 2k Standard + Win2k standard will not let me use more than 2 GB
> of
> > RAM for SQL. The average Physical Disk (_total) activity is 56% which is
> > considered very high.
> >
> > Any inputs would be appreciated on this setting while we order the SQL2K
> > Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
> > drives...
> >
> >
>
>|||"Anonymous" <Anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1D46A3CC-2D84-4FAA-AA97-24DDA8A27443@.microsoft.com...
> Thanks for your reply.
> I fully understand what you are saying (as it was also statement in my
> request). I am interested to know about he virtual memory portion of it.
From the application's point of view physical and virtual memory are just
the same. You must not sum them up. Physical mem in TaskMGR shows the
portion of the application that's actually in main memory while virtual
mem shows the total allocated size. Your case is optimal with regard to
swapping: the process is completely in physical mem.
As Mike said, your SQL Server will never use more than what it uses at the
moment unless you install enterprise edition.
You should use perfmon to determine what causes the high disk activity.
Regards
robert
> Thanks again
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > SQL Server Standard Edition is limited to 2GB of RAM. If you have
more, it
> > will not use it. You need to install SQL Server Expertise Edition to
use
> > more RAM (and set /3GB switch in your boot.ini). SQL Server knows it
must
> > leave space for the OS, so the .25 is left for the OS in the sub 2GB
memory
> > area.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> > news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> > > I have inherrited a sql 2k standard server running Win2K standard Ed
with
> > 3.5
> > > gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL
only and
> > has
> > > 4 CPUs.
> > >
> > > The "maximum memory" setting is set to 3500 MB and SQL is using
around
> > 1.75
> > > GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB).
Any
> > inputs
> > > on if the "maximum memory" setting should be left to 3500mb or
should it
> > be
> > > reduced to 2000 MB so SQL uses only the physical ram? I am aware
that the
> > > transaction log should not be on a raid 5 for performance reasons
and I
> > know
> > > that SQL 2k Standard + Win2k standard will not let me use more than
2 GB
> > of
> > > RAM for SQL. The average Physical Disk (_total) activity is 56%
which is
> > > considered very high.
> > >
> > > Any inputs would be appreciated on this setting while we order the
SQL2K
> > > Enterprise on Win2K Advanced Ed. and add an extra controller and 15
RPM
> > > drives...
> > >
> > >
> >
> >
> >

"Maximum Memory" setting in SQL Server properties

I have inherrited a sql 2k standard server running Win2K standard Ed with 3.5
gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and has
4 CPUs.
The "maximum memory" setting is set to 3500 MB and SQL is using around 1.75
GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any inputs
on if the "maximum memory" setting should be left to 3500mb or should it be
reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
transaction log should not be on a raid 5 for performance reasons and I know
that SQL 2k Standard + Win2k standard will not let me use more than 2 GB of
RAM for SQL. The average Physical Disk (_total) activity is 56% which is
considered very high.
Any inputs would be appreciated on this setting while we order the SQL2K
Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
drives...
Hi
SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
will not use it. You need to install SQL Server Expertise Edition to use
more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
area.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> I have inherrited a sql 2k standard server running Win2K standard Ed with
3.5
> gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and
has
> 4 CPUs.
> The "maximum memory" setting is set to 3500 MB and SQL is using around
1.75
> GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any
inputs
> on if the "maximum memory" setting should be left to 3500mb or should it
be
> reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
> transaction log should not be on a raid 5 for performance reasons and I
know
> that SQL 2k Standard + Win2k standard will not let me use more than 2 GB
of
> RAM for SQL. The average Physical Disk (_total) activity is 56% which is
> considered very high.
> Any inputs would be appreciated on this setting while we order the SQL2K
> Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
> drives...
>
|||Thanks for your reply.
I fully understand what you are saying (as it was also statement in my
request). I am interested to know about he virtual memory portion of it.
Thanks again
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
> will not use it. You need to install SQL Server Expertise Edition to use
> more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
> leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
> area.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> 3.5
> has
> 1.75
> inputs
> be
> know
> of
>
>
|||"Anonymous" <Anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1D46A3CC-2D84-4FAA-AA97-24DDA8A27443@.microsoft.com...
> Thanks for your reply.
> I fully understand what you are saying (as it was also statement in my
> request). I am interested to know about he virtual memory portion of it.
From the application's point of view physical and virtual memory are just
the same. You must not sum them up. Physical mem in TaskMGR shows the
portion of the application that's actually in main memory while virtual
mem shows the total allocated size. Your case is optimal with regard to
swapping: the process is completely in physical mem.
As Mike said, your SQL Server will never use more than what it uses at the
moment unless you install enterprise edition.
You should use perfmon to determine what causes the high disk activity.
Regards
robert
[vbcol=seagreen]
> Thanks again
> "Mike Epprecht (SQL MVP)" wrote:
more, it[vbcol=seagreen]
use[vbcol=seagreen]
must[vbcol=seagreen]
memory[vbcol=seagreen]
with[vbcol=seagreen]
only and[vbcol=seagreen]
around[vbcol=seagreen]
Any[vbcol=seagreen]
should it[vbcol=seagreen]
that the[vbcol=seagreen]
and I[vbcol=seagreen]
2 GB[vbcol=seagreen]
which is[vbcol=seagreen]
SQL2K[vbcol=seagreen]
RPM[vbcol=seagreen]

"Maximum Memory" setting in SQL Server properties

I have inherrited a sql 2k standard server running Win2K standard Ed with 3.
5
gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and ha
s
4 CPUs.
The "maximum memory" setting is set to 3500 MB and SQL is using around 1.75
GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any inputs
on if the "maximum memory" setting should be left to 3500mb or should it be
reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
transaction log should not be on a raid 5 for performance reasons and I know
that SQL 2k Standard + Win2k standard will not let me use more than 2 GB of
RAM for SQL. The average Physical Disk (_total) activity is 56% which is
considered very high.
Any inputs would be appreciated on this setting while we order the SQL2K
Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
drives...Hi
SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
will not use it. You need to install SQL Server Expertise Edition to use
more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
area.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> I have inherrited a sql 2k standard server running Win2K standard Ed with
3.5
> gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and
has
> 4 CPUs.
> The "maximum memory" setting is set to 3500 MB and SQL is using around
1.75
> GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any
inputs
> on if the "maximum memory" setting should be left to 3500mb or should it
be
> reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
> transaction log should not be on a raid 5 for performance reasons and I
know
> that SQL 2k Standard + Win2k standard will not let me use more than 2 GB
of
> RAM for SQL. The average Physical Disk (_total) activity is 56% which is
> considered very high.
> Any inputs would be appreciated on this setting while we order the SQL2K
> Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
> drives...
>|||Thanks for your reply.
I fully understand what you are saying (as it was also statement in my
request). I am interested to know about he virtual memory portion of it.
Thanks again
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
> will not use it. You need to install SQL Server Expertise Edition to use
> more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
> leave space for the OS, so the .25 is left for the OS in the sub 2GB memor
y
> area.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> 3.5
> has
> 1.75
> inputs
> be
> know
> of
>
>|||"Anonymous" <Anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1D46A3CC-2D84-4FAA-AA97-24DDA8A27443@.microsoft.com...
> Thanks for your reply.
> I fully understand what you are saying (as it was also statement in my
> request). I am interested to know about he virtual memory portion of it.
From the application's point of view physical and virtual memory are just
the same. You must not sum them up. Physical mem in TaskMGR shows the
portion of the application that's actually in main memory while virtual
mem shows the total allocated size. Your case is optimal with regard to
swapping: the process is completely in physical mem.
As Mike said, your SQL Server will never use more than what it uses at the
moment unless you install enterprise edition.
You should use perfmon to determine what causes the high disk activity.
Regards
robert
[vbcol=seagreen]
> Thanks again
> "Mike Epprecht (SQL MVP)" wrote:
>
more, it[vbcol=seagreen]
use[vbcol=seagreen]
must[vbcol=seagreen]
memory[vbcol=seagreen]
with[vbcol=seagreen]
only and[vbcol=seagreen]
around[vbcol=seagreen]
Any[vbcol=seagreen]
should it[vbcol=seagreen]
that the[vbcol=seagreen]
and I[vbcol=seagreen]
2 GB[vbcol=seagreen]
which is[vbcol=seagreen]
SQL2K[vbcol=seagreen]
RPM[vbcol=seagreen]

"Lock Pages in Memory" Service Account and SQL Agent

Microsoft recommends using the Lock Pages in Memory privilege for SQl
Server 2005 x64 running on Windows Server 2003 R2 x64.
If granting this privilege allows this user to Lock Pages in Memory,
should SQL Agent be changed to use a different service account that
does not have this privilege? (both SQL and Agent use the same account
currently)
I am thinking that if both SQL Server and SQL Agent are running under
a account with this privilege if they will conflict. Is this the case?Perhaps you are talking about this on BOL
"Although it is not required, we recommend locking pages in memory when
using 64-bit operating systems. For 32-bit operating systems, Lock pages in
memory permission must be granted before AWE is configured for SQL Server."
I do not see why there could be a conflict if both services are using the
same Windows account and this account has the Lock pages in memory permission
granted. It should be Ok.
(BOL note from
Enabling Memory Support for Over 4 GB of Physical Memory
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/34950321-2bfd-4679-8f1b-0a0a440eb443.htm
)
Hope this helps,
Ben Nevarez
"Neufusion" wrote:
> Microsoft recommends using the Lock Pages in Memory privilege for SQl
> Server 2005 x64 running on Windows Server 2003 R2 x64.
> If granting this privilege allows this user to Lock Pages in Memory,
> should SQL Agent be changed to use a different service account that
> does not have this privilege? (both SQL and Agent use the same account
> currently)
> I am thinking that if both SQL Server and SQL Agent are running under
> a account with this privilege if they will conflict. Is this the case?
>|||The main SQL Server service is specifically designed for managing large
amounts of memory and has an option to use AWE but the SQL Agent only works
like any other regular program as far as memory management's concerned
(Virtual Memory). Therefore there's no reason to think both services would
make large AWE allocations at startup.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Neufusion" <mikeymiller@.gmail.com> wrote in message
news:9a4a6b46-f949-4c38-a277-9757134c0714@.d21g2000prg.googlegroups.com...
> Microsoft recommends using the Lock Pages in Memory privilege for SQl
> Server 2005 x64 running on Windows Server 2003 R2 x64.
> If granting this privilege allows this user to Lock Pages in Memory,
> should SQL Agent be changed to use a different service account that
> does not have this privilege? (both SQL and Agent use the same account
> currently)
> I am thinking that if both SQL Server and SQL Agent are running under
> a account with this privilege if they will conflict. Is this the case?|||If I remember correctly, the "Lock pages in memory" setting only applies to
Enterprise, not Standard edition. not sure which you are running
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Neufusion" <mikeymiller@.gmail.com> wrote in message
news:9a4a6b46-f949-4c38-a277-9757134c0714@.d21g2000prg.googlegroups.com...
> Microsoft recommends using the Lock Pages in Memory privilege for SQl
> Server 2005 x64 running on Windows Server 2003 R2 x64.
> If granting this privilege allows this user to Lock Pages in Memory,
> should SQL Agent be changed to use a different service account that
> does not have this privilege? (both SQL and Agent use the same account
> currently)
> I am thinking that if both SQL Server and SQL Agent are running under
> a account with this privilege if they will conflict. Is this the case?|||This was true for SQL 2000 but not SQL 2005
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:Oh8WQgnYIHA.3964@.TK2MSFTNGP03.phx.gbl...
> If I remember correctly, the "Lock pages in memory" setting only applies
> to Enterprise, not Standard edition. not sure which you are running
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Neufusion" <mikeymiller@.gmail.com> wrote in message
> news:9a4a6b46-f949-4c38-a277-9757134c0714@.d21g2000prg.googlegroups.com...
>> Microsoft recommends using the Lock Pages in Memory privilege for SQl
>> Server 2005 x64 running on Windows Server 2003 R2 x64.
>> If granting this privilege allows this user to Lock Pages in Memory,
>> should SQL Agent be changed to use a different service account that
>> does not have this privilege? (both SQL and Agent use the same account
>> currently)
>> I am thinking that if both SQL Server and SQL Agent are running under
>> a account with this privilege if they will conflict. Is this the case?
>|||http://support.microsoft.com/kb/918483/en-us
Note For 64-bit systems, SQL Server 2005 Enterprise Edition is the only
edition that is designed to use lock pages in memory.
Am I misreading? I have a 3 node, 2 instance cluster my customer is about
to upgrade for this very reason...
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:utExsLrYIHA.4196@.TK2MSFTNGP04.phx.gbl...
> This was true for SQL 2000 but not SQL 2005
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:Oh8WQgnYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> If I remember correctly, the "Lock pages in memory" setting only applies
>> to Enterprise, not Standard edition. not sure which you are running
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Neufusion" <mikeymiller@.gmail.com> wrote in message
>> news:9a4a6b46-f949-4c38-a277-9757134c0714@.d21g2000prg.googlegroups.com...
>> Microsoft recommends using the Lock Pages in Memory privilege for SQl
>> Server 2005 x64 running on Windows Server 2003 R2 x64.
>> If granting this privilege allows this user to Lock Pages in Memory,
>> should SQL Agent be changed to use a different service account that
>> does not have this privilege? (both SQL and Agent use the same account
>> currently)
>> I am thinking that if both SQL Server and SQL Agent are running under
>> a account with this privilege if they will conflict. Is this the case?
>>
>

"Insufficient memory available" on SQL 2000

Hi All,
We have SQL 2000 ent and Windows 2003 , in a few day , restart many
time the error message in event log is "Error: 17803, Severity: 20, State:
14
Insufficient memory available."
i try open SQL profiler and use performance monitor , but i don't know how
to fix ? in profiler display error log , but i don't know is server problem
or application problem , how to check the problem ? help
SQL 2000 Ent ,enable AWE
Windows 2003 ent enable PAE
RAM : 16 GB
In Profiler error :
" 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address found."
"2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
Free=949769 Procedures=7
Inram=0 Dirty=236616 Kept=0
I/O=0, Latched=194, Other=107327"
"2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
Target=1415936 Hashed=344137
InternalReservation=360 ExternalReservation=0 Min Free=128 Visible= 191272"
"2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
TotalPages=7 InUsePages=4"
"2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029 OS
Reserved=3168
OS Committed=3112
OS In Use=3108
Query Plan=96903 Optimizer=1
General=24144
Utilities=160 Connection=3834 "
"2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029 OS
Reserved=3168
OS Committed=3112
OS In Use=3108
Query Plan=96903 Optimizer=1
General=24144
Utilities=160 Connection=3834"
"2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0 Waiting=0
Maximum=52143 Available=52143"
"Error: 701, Severity: 17, State: 132"
"2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address found."
"2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
Free=949770 Procedures=7
Inram=0 Dirty=236616 Kept=0
I/O=0, Latched=194, Other=107327"
"2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
Target=1415936 Hashed=344137
InternalReservation=360 ExternalReservation=0 Min Free=128 Visible= 191272"
"2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
TotalPages=7 InUsePages=4"
......
how to fix this , i need restart the server mant time on everyday , HELP !!!Check out the following stuff from System Monitor:
Memory: Available MBytes
Memory: Page Faults\sec
MSSQL$<instance_name>: Buffer Manager: Buffer cache hit ratio
MSSQL$<instance_name>: Buffer Manager: Page life expectancy
MSSQL$<instance_name>: General Statistics: User Connections
Physical Disk: % Disk Time
Physical Disk: Disk Read Bytes\sec
Physical Disk: Disk Write Bytes\sec
Physical Disk: Avg. Disk Queue Length
Processor: % Processor Time
System: Processor Queue Length
Did you restarted your Windows Server after setting up PAE and same for SQL
Server's AWE setting. You need to restart your SQL Server service to take
effect this setting.
Please let me know the values of the counters above. (Don't analyze only for
2-3 mins. Give them at least 1 hour to work and do it in peak-hours)
Ekrem Önsoy
"pcnetnet" <pcnetnet@.yahoo.com.hk> wrote in message
news:e3Te6Yh%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> We have SQL 2000 ent and Windows 2003 , in a few day , restart many
> time the error message in event log is "Error: 17803, Severity: 20,
> State:
> 14
> Insufficient memory available."
> i try open SQL profiler and use performance monitor , but i don't know how
> to fix ? in profiler display error log , but i don't know is server
> problem
> or application problem , how to check the problem ? help
> SQL 2000 Ent ,enable AWE
> Windows 2003 ent enable PAE
> RAM : 16 GB
> In Profiler error :
> " 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address
> found."
> "2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
> Free=949769 Procedures=7
> Inram=0 Dirty=236616 Kept=0
> I/O=0, Latched=194, Other=107327"
> "2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
> Target=1415936 Hashed=344137
> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=> 191272"
> "2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
> TotalPages=7 InUsePages=4"
> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
> OS
> Reserved=3168
> OS Committed=3112
> OS In Use=3108
> Query Plan=96903 Optimizer=1
> General=24144
> Utilities=160 Connection=3834 "
> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
> OS
> Reserved=3168
> OS Committed=3112
> OS In Use=3108
> Query Plan=96903 Optimizer=1
> General=24144
> Utilities=160 Connection=3834"
> "2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0
> Waiting=0
> Maximum=52143 Available=52143"
> "Error: 701, Severity: 17, State: 132"
> "2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address
> found."
> "2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
> Free=949770 Procedures=7
> Inram=0 Dirty=236616 Kept=0
> I/O=0, Latched=194, Other=107327"
> "2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
> Target=1415936 Hashed=344137
> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=> 191272"
> "2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
> TotalPages=7 InUsePages=4"
> ......
> how to fix this , i need restart the server mant time on everyday , HELP
> !!!
>
>
>
>|||pcnetnet (pcnetnet@.yahoo.com.hk) writes:
> We have SQL 2000 ent and Windows 2003 , in a few day , restart
> many time the error message in event log is "Error: 17803, Severity:
> 20, State: 14 Insufficient memory available." i try open SQL profiler
> and use performance monitor , but i don't know how to fix ? in profiler
> display error log , but i don't know is server problem or application
> problem , how to check the problem ? help
That looks really bad. I was about to suggest that you should open a
case with Microsoft, but as I searched in Books Online for error
17803, I found something interesting: it's listed in a section for
for error codes listed by Open Data Services. So maybe this is due
to a memory leak in an extended stored procedure?
Do you if there are any extended stored procedures installed on your
system (beside those that ship with SQL Server)? Would it be possible
to keep them from running for a while to see if the problem goes away.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi ekrem,
i have restart the server to take the effect , but the same case, i have
the momitor file , how to post to this ?
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:4F41E891-DC4A-4D2C-89C3-52441BDB998A@.microsoft.com...
> Check out the following stuff from System Monitor:
> Memory: Available MBytes
> Memory: Page Faults\sec
> MSSQL$<instance_name>: Buffer Manager: Buffer cache hit ratio
> MSSQL$<instance_name>: Buffer Manager: Page life expectancy
> MSSQL$<instance_name>: General Statistics: User Connections
> Physical Disk: % Disk Time
> Physical Disk: Disk Read Bytes\sec
> Physical Disk: Disk Write Bytes\sec
> Physical Disk: Avg. Disk Queue Length
> Processor: % Processor Time
> System: Processor Queue Length
> Did you restarted your Windows Server after setting up PAE and same for
> SQL Server's AWE setting. You need to restart your SQL Server service to
> take effect this setting.
> Please let me know the values of the counters above. (Don't analyze only
> for 2-3 mins. Give them at least 1 hour to work and do it in peak-hours)
>
> --
> Ekrem Önsoy
>
> "pcnetnet" <pcnetnet@.yahoo.com.hk> wrote in message
> news:e3Te6Yh%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> We have SQL 2000 ent and Windows 2003 , in a few day , restart
>> many
>> time the error message in event log is "Error: 17803, Severity: 20,
>> State:
>> 14
>> Insufficient memory available."
>> i try open SQL profiler and use performance monitor , but i don't know
>> how
>> to fix ? in profiler display error log , but i don't know is server
>> problem
>> or application problem , how to check the problem ? help
>> SQL 2000 Ent ,enable AWE
>> Windows 2003 ent enable PAE
>> RAM : 16 GB
>> In Profiler error :
>> " 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address
>> found."
>> "2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
>> Free=949769 Procedures=7
>> Inram=0 Dirty=236616 Kept=0
>> I/O=0, Latched=194, Other=107327"
>> "2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
>> Target=1415936 Hashed=344137
>> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=>> 191272"
>> "2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
>> TotalPages=7 InUsePages=4"
>> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
>> OS
>> Reserved=3168
>> OS Committed=3112
>> OS In Use=3108
>> Query Plan=96903 Optimizer=1
>> General=24144
>> Utilities=160 Connection=3834 "
>> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
>> OS
>> Reserved=3168
>> OS Committed=3112
>> OS In Use=3108
>> Query Plan=96903 Optimizer=1
>> General=24144
>> Utilities=160 Connection=3834"
>> "2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0
>> Waiting=0
>> Maximum=52143 Available=52143"
>> "Error: 701, Severity: 17, State: 132"
>> "2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address
>> found."
>> "2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
>> Free=949770 Procedures=7
>> Inram=0 Dirty=236616 Kept=0
>> I/O=0, Latched=194, Other=107327"
>> "2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
>> Target=1415936 Hashed=344137
>> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=>> 191272"
>> "2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
>> TotalPages=7 InUsePages=4"
>> ......
>> how to fix this , i need restart the server mant time on everyday , HELP
>> !!!
>>
>>
>>
>>
>|||Attach file to your message and send it.
I hope it's not a big issue as Erland mentioned.
--
Ekrem Önsoy
"Pcnetnet" <pcnetnet@.yahoo.com.hk> wrote in message
news:%232SKRom%23HHA.748@.TK2MSFTNGP04.phx.gbl...
> Hi ekrem,
> i have restart the server to take the effect , but the same case, i
> have the momitor file , how to post to this ?
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:4F41E891-DC4A-4D2C-89C3-52441BDB998A@.microsoft.com...
>> Check out the following stuff from System Monitor:
>> Memory: Available MBytes
>> Memory: Page Faults\sec
>> MSSQL$<instance_name>: Buffer Manager: Buffer cache hit ratio
>> MSSQL$<instance_name>: Buffer Manager: Page life expectancy
>> MSSQL$<instance_name>: General Statistics: User Connections
>> Physical Disk: % Disk Time
>> Physical Disk: Disk Read Bytes\sec
>> Physical Disk: Disk Write Bytes\sec
>> Physical Disk: Avg. Disk Queue Length
>> Processor: % Processor Time
>> System: Processor Queue Length
>> Did you restarted your Windows Server after setting up PAE and same for
>> SQL Server's AWE setting. You need to restart your SQL Server service to
>> take effect this setting.
>> Please let me know the values of the counters above. (Don't analyze only
>> for 2-3 mins. Give them at least 1 hour to work and do it in peak-hours)
>>
>> --
>> Ekrem Önsoy
>>
>> "pcnetnet" <pcnetnet@.yahoo.com.hk> wrote in message
>> news:e3Te6Yh%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> We have SQL 2000 ent and Windows 2003 , in a few day , restart
>> many
>> time the error message in event log is "Error: 17803, Severity: 20,
>> State:
>> 14
>> Insufficient memory available."
>> i try open SQL profiler and use performance monitor , but i don't know
>> how
>> to fix ? in profiler display error log , but i don't know is server
>> problem
>> or application problem , how to check the problem ? help
>> SQL 2000 Ent ,enable AWE
>> Windows 2003 ent enable PAE
>> RAM : 16 GB
>> In Profiler error :
>> " 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address
>> found."
>> "2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
>> Free=949769 Procedures=7
>> Inram=0 Dirty=236616 Kept=0
>> I/O=0, Latched=194, Other=107327"
>> "2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
>> Target=1415936 Hashed=344137
>> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=>> 191272"
>> "2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
>> TotalPages=7 InUsePages=4"
>> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
>> OS
>> Reserved=3168
>> OS Committed=3112
>> OS In Use=3108
>> Query Plan=96903 Optimizer=1
>> General=24144
>> Utilities=160 Connection=3834 "
>> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
>> OS
>> Reserved=3168
>> OS Committed=3112
>> OS In Use=3108
>> Query Plan=96903 Optimizer=1
>> General=24144
>> Utilities=160 Connection=3834"
>> "2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0
>> Waiting=0
>> Maximum=52143 Available=52143"
>> "Error: 701, Severity: 17, State: 132"
>> "2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address
>> found."
>> "2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
>> Free=949770 Procedures=7
>> Inram=0 Dirty=236616 Kept=0
>> I/O=0, Latched=194, Other=107327"
>> "2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
>> Target=1415936 Hashed=344137
>> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=>> 191272"
>> "2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
>> TotalPages=7 InUsePages=4"
>> ......
>> how to fix this , i need restart the server mant time on everyday , HELP
>> !!!
>>
>>
>>
>>
>|||Did you ever get a fix to your problem? We are also running SQL 2000 sp4
with the awe hot fix. We are on windows 2003 sp2. We've had this happen
several times on a production server. We tried killing some spids that we
thought might be causing the problem - but that didn't help. We had to
reboot the server.
We have a case open with microsoft - but so far they are telling us its
beyond SQL Server's scope. SQL Server is using all the memory except for a
little we have set aside (1 gb out of 4 gb) for the OS, etc.
Let me know what you found.
"pcnetnet" wrote:
> Hi All,
> We have SQL 2000 ent and Windows 2003 , in a few day , restart many
> time the error message in event log is "Error: 17803, Severity: 20, State:
> 14
> Insufficient memory available."
> i try open SQL profiler and use performance monitor , but i don't know how
> to fix ? in profiler display error log , but i don't know is server problem
> or application problem , how to check the problem ? help
> SQL 2000 Ent ,enable AWE
> Windows 2003 ent enable PAE
> RAM : 16 GB
> In Profiler error :
> " 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address found."
> "2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
> Free=949769 Procedures=7
> Inram=0 Dirty=236616 Kept=0
> I/O=0, Latched=194, Other=107327"
> "2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
> Target=1415936 Hashed=344137
> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=> 191272"
> "2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
> TotalPages=7 InUsePages=4"
> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029 OS
> Reserved=3168
> OS Committed=3112
> OS In Use=3108
> Query Plan=96903 Optimizer=1
> General=24144
> Utilities=160 Connection=3834 "
> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029 OS
> Reserved=3168
> OS Committed=3112
> OS In Use=3108
> Query Plan=96903 Optimizer=1
> General=24144
> Utilities=160 Connection=3834"
> "2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0 Waiting=0
> Maximum=52143 Available=52143"
> "Error: 701, Severity: 17, State: 132"
> "2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address found."
> "2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
> Free=949770 Procedures=7
> Inram=0 Dirty=236616 Kept=0
> I/O=0, Latched=194, Other=107327"
> "2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
> Target=1415936 Hashed=344137
> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=> 191272"
> "2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
> TotalPages=7 InUsePages=4"
> .......
> how to fix this , i need restart the server mant time on everyday , HELP !!!
>
>
>
>
>