out of memory

Question

hello,

we are trying to determine root cause of server running out of memory.

we are on:
 Microsoft SQL Server 2005 - Ent Ed 32-bit  SP4
 mutltiple dbs running used for web applications

On Jun-9:
 - SP4 was applied and
 - max memory was set to 10GB (out of 12GB)

Starting Jun-29 we started seeing these messages frequently:
AppDomain 243 (db_name.dbo[runtime].242) is marked for unload due to memory pressure

and on Jul-10 it finally reached "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 589824" and "There is insufficient system memory to run this query" and server had to be rebooted.

We are still continually receiving the "marked for unload due to memory pressure" messages (10 times already for today).

Is this the possible cause of the system running out of memory?
and if so, how can we prevent it from happening again?

we ran perfmon for 2 days and observed that:
 AvailableMbytes is good (did not dip below 8 GB)
 memory grants pending - remained at 0

what else can we look at to determine the root cause of the system running out of memory?

 thank you

Answer 1

Would you please check-out below links

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d5c8f848-260e-4f88-8d97-2a0b06363a39/

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
My Blog
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

Answer 2

hello,

the first article pertains to setting max memory, which was implemented on the server in the beginning of the month.  Max mem was set to 10 GB, leaving 2 GB for the os.

so it was odd that messages about memory pressure started occurring after this change.

but if system is unloading applications due to memory pressure, why would the server lead to 'system out of memory'?

most of what i find on the internet about 'unload due to memory pressure' point to CLR functions. does that mean continual use of this led to the server running out of memory?

could somebody point me to what perfmon counters i should collect to pinpoint the issue.

a previous perfmon collection showed memory was good.

thank you

Answer 3

The "max memory" option only controlls the buffer pool space.  There are many other memory allocations in SQL Server which do not use this setting.  You need to look at your actual physical RAM and see if you are paging.  If you are, you need to reduce the max memory setting until the paging stops.

Answer 4

Since its failing on FAIL_VIRTUAL_RESERVE, I think it is related to Memory to Leave bit of UserVAS.

Do you run Linked Servers or CLR or extended Stored procedures which uses the MTL(memory to leave) bit of the memory. Any Cached plan more than 8KB also are stored in MTL.

I think you will need to use -g parameter which can be used to increase MTL memory. By allocating more memory to MTL, we are stealing some OS which needs to be compensated by reducing max memory setting which is used by Buffer Pool.

Hope this helps...

---

Vamshi SQL DBA(MCITP) My Blogs: http://sqlserver-dba.co.uk http://sql-developers.co.uk/

Answer 5

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/e6732f32-083e-4b04-9b3b-78b28fbd004f

---

M.Janzou