Unable to resolve memory errors

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Unable to resolve memory errors

Author

Topic

swensor
Starting Member

4 Posts

Posted - 2009-08-05 : 11:02:47

Hey all,

Something about my SQL 2000 configuration is causing it to spontaneously generate insufficient memory available errors. It runs without a problem at least 95% of the time, then randomly a query will fail to connect, generating these exact(~) messages each time in the error log:

1. WARNING: failed to reserve contiguous memory of Size = 65536
2. Buffer Distribution: Stolen=13825 Free=93 Procedures=7519
Inram=0 Dirty=382 Kept=0
I/O=0, Latched=166, Other=184871
3. Buffer Counts: Commited=206856 Target=206856 Hashed=185419
InternalReservation=676 ExternalReservation=0 Min Free=32 Visible= 206856
4. Procedure Cache: TotalProcs=5602 TotalPages=7519 InUsePages=3408
5. Dynamic Memory Manager: Stolen=21344 OS Reserved=26840
OS Committed=26818
OS In Use=26815
Query Plan=7509 Optimizer=0
General=1282
Utilities=12 Connection=39265
6. Global Memory Objects: Resource=996 Locks=54
SQLCache=150 Replication=2
LockBytes=2 ServerGlobal=20
Xact=61
7. Query Memory Manager: Grants=0 Waiting=0 Maximum=142218 Available=142218
8. Error: 17803, Severity: 20, State: 4
9. Insufficient memory available..

It is strange this error will happen on every query attempt for a few minutes, then go away for maybe an hour or so. A few things about my server. I am connecting via .NET SQL Client, on .Net 1.1, Windows Server 2003 Enterprise Edition. Sql 2000 Enterprise, just upgraded to build 2187.

I have 4.75 GB of RAM and 2 cores of a Xeon @ 3.0 GHz. This seems to happen when page file usage is high (over 3 GB). My page file is set to a max of 5 GB right now I think.

I have looked at various hotfixes (installed sp4 and build 2187, so i should have everything needed), articles, forums, etc. on the issue and can't seem to find anything that identifies this problem specifically. The error gets thrown when there is over 1.5 GB of free physical RAM. I dont understand how its possible the server cant find 64 K of contiguous space when there are almost 2 free gigs of memory.

Some notes on my SQL 2000 config:
Dynamically Configure SQL memory: Min 1024, Max 3284
No reserved physical memory for SQL Server
Uses one of two CPU cores, boosts SQL priority on Windows

Does anyone have any ideas? Could my connection pool be flooded? How do I check? Should I make it bigger or look for dropped connections? What else can I monitor within SQL to see why it is so poor at managing memory? Thanks in advance!!

swensor
Starting Member

4 Posts

Posted - 2009-08-05 : 11:28:09

Looking into this I think theres a good chance it has to do with the high numbers I have in Dynamic Memory manager, consuming all of my MemToLeave. I see over 42,000 buffers for Connection, this seems excessive. I do have hundreds of apps making many connections at a time, is it possible to reduce this figure to allocate more memory to MemToLeave??

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-08-07 : 22:25:20

You need to give us results from Memory Counters in Perfmon?

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2009-08-10 : 16:54:15

There is a configuration switch that will reserve a larger memtoleve region. (it is precalculated when sql server starts up)

-g400 is my running value. It occasionally has the same issue now.

I posted everything I found out about this in a thread.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=122562

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources