Insufficient Memory error

[divinyl]

Hi all

I've been getting this error on one of my SQL Servers for the last few days - this is from the event log:

WARNING: Failed to reserve contiguous memory of Size= 1179648.
2006-07-31 11:06:59.36 spid210 Buffer Distribution: Stolen=2107 Free=665 Procedures=25406
Inram=0 Dirty=9445 Kept=0
I/O=0, Latched=43, Other=168990
2006-07-31 11:06:59.36 spid210 Buffer Counts: Commited=206656 Target=206656 Hashed=178478
InternalReservation=559 ExternalReservation=0 Min Free=128 Visible= 206656
2006-07-31 11:06:59.36 spid210 Procedure Cache: TotalProcs=17111 TotalPages=25406 InUsePages=10472
2006-07-31 11:06:59.36 spid210 Dynamic Memory Manager: Stolen=27513 OS Reserved=1000
OS Committed=978
OS In Use=926
Query Plan=25292 Optimizer=0
General=1930
Utilities=12 Connection=1062
2006-07-31 11:06:59.36 spid210 Global Memory Objects: Resource=1216 Locks=78
SQLCache=485 Replication=2
LockBytes=2 ServerGlobal=25
Xact=26
2006-07-31 11:06:59.36 spid210 Query Memory Manager: Grants=0 Waiting=0 Maximum=145570 Available=145570
2006-07-31 11:06:59.38 spid210 Error: 17803, Severity: 20, State: 12
2006-07-31 11:06:59.38 spid210 Insufficient memory available..

Now my transaction log backups are failing as well... the server is a dedicated sql box running Windows 2003, SQL 2000 SP4 - standard configuration (no fancy memory tweaks or anything like that). It's got 3 GB of memory which should be more than enough...

I've run performance monitor (not at the time of the error) and noticed that SQL Buffer Manager: Free Pages is very low (between 1 and 3 mb) whereas it runs at 100% on all my good performing servers.. i know this is an issue but the question is how best to fix this? I've spent the day reasearching this error but there's a lot of information and it's figuring out what best applies to my situation.

Can anyone provide some good tips on how to address this, like assigning more memory to sql server (and any downfalls to this)?

Thanks
Div

 

[katgirl]

Is your server set to use memory dynamically? You can set this in server properties in EM.

3 GB of memory isn't that much when you consider 1/2 of that goes to the OS.

 

[divinyl]

Hi KatGirl

Thanks for your quick response. Yes, the server is set to use dynamic memory.. and you are right, judging from perf mon sql server is using about half that. Do you think it's worth increasing the amount of mem allocated to SQL server and if so, what implications are there considering this is a live server?

Thanks!!
Div

 

[katgirl]

For better or worse, 2 GB goes to the OS, and there's nothing you can do to change that. So that leaves 1 GB for SQL Server.

You've already set memory dynamically... I'm not sure that you can do anything else to directly increase memory to SQL Server... nothing that I can recommend to you at any rate. Perhaps you can gain some advantage by tuning your queries, but that is a more long-range solution.

Sounds like it is time to add more memory or upgrade the server.

 

[mrdenny]

2 GB goes to the OS???

What do you have running on your SQL Servers? Everyone of mine are running happly on 512 Megs of RAM for the OS with the remaining for the SQL Server.

There is some info here:

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=62&threadid=47814&enterthread=y

And here:

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11140

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[divinyl]

Thanks guys... i have nothing major running on my server at all. It is almost identical to another sql server i have in terms of build and the other has no problems... however i just had a look at the virtual memory setting and there was hardly nothing allocated. I've changed this appropriately and rebooted the server - but i will have to wait to see if its had any effect because the error was intermittent.

Having said that, the problem should not have to do with virtual memory but rather what would cause it to dig into virtual memory in the first place!!!

Question - do you guys know why my SQL Buffer Manager: Free Pages counter is so low on this server?? Running at around 10 mb when most of my other servers are way up high.... i cant find anything on the web indicating what to do if this counter is low!!!!

Thanks
Div

 

[katgirl]

mrdenny,

I just read yesterday, that yes, 2 GB goes to OS... Later when I have time I will attempt to locate this information, which I have seen in several places. So I'm not making this up.

 

[katgirl]

Ok, here is one reference from "SQL Server Magazine", May 2006 issue, from The Essential Guide to 64-bit server, verbatim:

"Under 32-bit Windows, 2GB is reserved the operating system, leaving only 2GB for all the applications that must split the 2GB of appliation memory." [this is with 4GB total - my note]

This is why it's recommended not to run other apps on your db server.

I've also read this in other places as well.

 

[katgirl]

All our servers here have a min of 4 GB, so I don't have any other frame of reference. I don't know what to say about your situation (less memory). I couldn't imagine running on less than 4GB - we'd be out of business here.

 

[mrdenny]

I beleive that the SQL Server Magazine folks are probably confussed. Windows does not dominate the first 2 Gigs of RAM. If it did no server with 2 Gigs of RAM or less would function.

I beleive that this is what they are referring to Article 266096

By default, Windows 2000 Advanced Server reserves 2 GB of virtual address space for the kernel, and allows user mode processes (such as the Exchange 2000 information store process, Store.exe) to use 2 GB of virtual address space.

The MSKB article that explains the 3GB and PAE switches can be found here

http://support.microsoft.com/default.aspx?scid=kb;EN-US;283037.

When reading SQL Server Magazine keep in mind that it's not written by Microsoft. It's published by Penton Media, Inc. Over the last several months I have seen incorrect information about SQL 2005 published in the magazine. (I don't actually pay for my subscription, which is why I still get it.)

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katgirl]

yes, it's possible the article is wrong... but i am certain i've also read this in other places... perhaps this 2gb only applies with certain configurations... i will have to investigate this further.....!

 

[mrdenny]

When it comes to stuff like this it's usually better to trust Microsoft as they wrote the software, they usually know how it works better than other people.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[divinyl]

Well - i think i found out what the problem is. For some odd reason my paging file was set to a minimum of 30 mb and a max of 50!! I don't know how or why but i changed this to GB values, rebotted the machine and it seems to be doing ok - for NOW. I didn't think SQL would use virtual memory unless it was running low on physical memory... do you guys know more about this??

Also no one answered my question about the free pages counter and what it means if the values are low - because they still are even after my modifications. Does anyone know more about this?

Thanks,
Div

 

[katgirl]

Free pages

If this counter is well below 640 pages (about 5 mb), then sql server is either running low on physical memory or the active portion of the buffer cache is very large.

from "SQL Server Query Performance Tuning Distilled", pg 25

or check out BOOKS ONLINE

these are your best resources for the types of questions you have

 

[mrdenny]

SQL Server doesn't actually use the Page File. SQL knows that the virtual memory is very slow and instead of using the virtual memory it will simply clear space in it's memory pool by uncaching data.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[divinyl]

REALLY??? So you are saying that the change i made to the size of the paging file should have no effect whatsoever on the problem im having??? Or perhaps by increasing the paging file its helped the OS which has in turn helped SQL?

I need to understand this..

By the way KatGirl, books online says nuthin at all about free pages unfortunately! I know a lot of DBAs have complained that there is not much information out there about what acceptable counter values should be...

Div

 

[katgirl]

anyhow, i do recommend sajal dam's book it is excellent for understanding all aspects of tuning

 

[mrdenny]

Changing the page file (and there by rebooting) probably helped the OS, which then made SQL more stable.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[divinyl]

yeah, makes sense. Thanks guys.

Div