Hi,
I am getting the above error on the production box which has SQL 2000,
sp3a build 8.00.780 with AWE and PAE enabled and SQL Server (Active/Passive
Cluster) has been allocated 7GB out of the total 8GB on the box.SQL Server
froze for almost 10 minutes and it came back fine after that.But things
started slowing down after that.But this is the first time we are getting
this error since we went live since March 2004.I did see an Article Id :
316749 which says it ran out of Virtual address space.
My question is
1) Has anybody had this problem before and had to change the -g switch ?
2) Is this some kind of Memory leak ?
3) Is there anything that i have to specifically monitor on the perfmon
counters in addition to (Pages/sec,CPU,Disk Queue Length,Total Server
Memory,Target Server Memory) before i could anticipate this kind of
situation ?

Thanks for all your help

Try reducing memory allocated for SQL to 6GB. Are you running anything else
on the SQL host computer besides SQL server that may consume memory or any
external xps. I doubt it is a memory leak in SQL, but it may be a memory
fragmentation issue.

You may want to bring SQL up to build 818, which is the latest security
update. It may not help your problem, but it may avoid other issues.

If this continues, you may want to open a case with Product Support
Services. They have some really good diagnostic tools to find out what is
happening with a not-so-perfect server.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"kneel" <kvatsan (AT) comcast (DOT) net> wrote

Thanks Geoff..This is a dedicated SQL Server box..But they do have the Tape
backup software (Galaxy) and also HP's open view (which has been disabled
for a long time) running on this box..BTW going to build 818 is really not
an option..Maybe if it happens again then we were thinking about opening up
a case with Microsoft..I was also monitoring the pages/sec counter and there
was absolutely no paging at all.I thought leaving 1GB for other applications
on a dedicated SQL Server box should be good enough.

Thanks,

"Geoff N. Hiten" <SQLCraftsman (AT) gmail (DOT) com> wrote

The tape backup software is going to want a lot of IO buffer space. I would
definitely lower the allocated memory to 56GB and see what happens. I
understand about strict policies on patching, but my personal philosophy is
any potential instabilities are less likely than a security problem caused
by NOT patching.


--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"Karthik" <kvatsan (AT) comcast (DOT) net> wrote

I meant 6GB. I should know better than to post before I have had sufficient
caffeine.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"Geoff N. Hiten" <SQLCraftsman (AT) gmail (DOT) com> wrote

We have encountered a similar problem lately with the same error message
plus a few more. We are being put through the same exercise of installing
the standard troubleshooting/disgnostic tools, and wait for the problem to
occur again so that more info can be captured for Microsoft. But the prime
suspect is the fragmentation in MemToLeave.

Also, we had problems with a certain tape backup system causing memory
issues. So I wouldn't rule out the tape backup software easily. But you
should really engage Microsoft support in this case.

By the way, I don't think it had anything to do with SQL clustering.

Linchi

"Karthik" <kvatsan (AT) comcast (DOT) net> wrote

Thanks Linchi..It hasnt happened since then..The tape backup software doesnt
run for us in the mornings and we also have shutdown the HP's Openview which
i have never liked it from the beginning.
Coming back to Memtoleave area did they tell you what would cause the
fragmentation to happen.Did they talk anything about using -g parameter as
startup ? Just wondering.

Thanks

"Linchi Shea" <linchi_shea (AT) NOSPAM (DOT) ml.com> wrote

MemToLeave can become fragmented if it's heavily used (e.g. if you are
running XP's, in-process linked servers. and so on). The issue is, when the
process needs to allocate a chunk of contiguous, but can't find one, it
gripes and may just crash. Using -g with a larger than the default size may
help. But if you have a real MemToLeave fragmentation problem, it'll just
postpone the inevitable. The best thing to do to maintain a stable SQL
Server instance is not to run any third-party DLL's in MemToLeave, which is
of course easier said than done.

Linchi

"kneel" <kvatsan (AT) comcast (DOT) net> wrote