out of memory

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » out of memory

12 posts, Page 1 of 2

1 2 »»

out of memory

Rate Topic

Display Mode

Topic Options

Author

Message

sierra4

Posted Monday, July 30, 2012 3:16 PM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:36 AM
Points: 34, Visits: 416

hello,

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

Post #1337569

SQLRNNR

Posted Monday, July 30, 2012 3:18 PM

SSCoach

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:46 PM
Points: 18,732, Visits: 12,329

Memory is being flushed. You can try enabling "Lock Pages in Memory"

This should help.

I am curious if you are running a bunch of other applications on the servers (other than SQL Server) that would require more memory.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1337571

GilaMonster

Posted Monday, July 30, 2012 4:32 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

Locked pages won't help.

You're getting mem_to_leave pressure. (the portion of the base 2GB that is reserved for thing like thread stacks, CLR, backup buffers, etc before the buffer pool is allocated)

Workaround: use the -g flag to increase the mem_to_leave that's reserved and restart SQL when the symptoms start (I've seen a case where SQL had to be restarted every 2 weeks because of this)
Solution: move to 64-bit SQL.

Chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Has more CLR been added recently? More linked server usage?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1337601

SQLRNNR

Posted Monday, July 30, 2012 4:38 PM

SSCoach

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:46 PM
Points: 18,732, Visits: 12,329

d'oh. Thanks Gail.

I was getting this about 3 months ago on 2012 and thought I had done LPIM. But looking at notes, I realized that all I had changed was Max memory to reduce to the 2GB limit. Once I did that, the errors stopped. I couldn't do the LPIM because of the version of Windows 7 (still need to fix that). Stopping and restarting services only worked briefly for me.

I think the best avenue for this is to get to a 64 bit server.

Post #1337603

sierra4

Posted Tuesday, July 31, 2012 8:09 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:36 AM
Points: 34, Visits: 416

hi,

Most of the 'marked for unload due to memory pressure' articles i see point to use of CLR functions. would this have eventually caused the server itself to run out of memory?

There is only SQL Server running here, with multiple small/mid-size dbs used for web applications.

thanks!

Post #1337917

GilaMonster

Posted Tuesday, July 31, 2012 10:54 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

sage8 (7/31/2012)

hi,

Most of the 'marked for unload due to memory pressure' articles i see point to use of CLR functions. would this have eventually caused the server itself to run out of memory?

Not on 32 bit. The limitation on mem_to_leave (384 MB by default) won't allow CLR to use more than that.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1338047

sierra4

Posted Wednesday, August 01, 2012 8:33 PM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:36 AM
Points: 34, Visits: 416

Of the 12 GB RAM on the system, 10 GB is set as the max size for SQL Server.

From my understanding, the CLR functions are using memory from the 2 GB not allocated to SQL Server.
Would setting the max size for SQL Server to a lower value of 8 GB reduce the unload occurrences?

However we soon ran into insufficient memory again.
2012-07-30 19:19:36.27 spid1s AppDomain 265 (db_prodctlg.dbo[runtime].264) unloaded.
2012-07-30 19:19:59.78 spid435 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 589824
:
2012-07-30 19:19:59.82 spid435 There is insufficient system memory to run this query.

At this point I'm not sure if the memory pressure is internal or external.

what tools do you recommend we can use to identify the process consuming the memory?

thank you!

Post #1338921

GilaMonster

Posted Wednesday, August 01, 2012 9:30 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

Not on 32 bit. On 32 bit they use the Mem_to_leave area which by default is 384MB.

Setting max server memory lower will accomplish nothing on 32-bit other than to reduce the size of the buffer pool an increase free memory. It will not increase the CLR available memory (which has to come out of the 2GB virtual address space and is limited to 384MB unless you increase the size of mem_to_leave)

As I said earlier, the workaround is to use the -g flag to increase the mem_to_leave that's reserved and restart SQL when the symptoms start (I've seen a case where SQL had to be restarted every 2 weeks because of this)
The solution is to move to 64-bit SQL.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1338928

sierra4

Posted Thursday, August 02, 2012 6:59 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:36 AM
Points: 34, Visits: 416

thank you, that clarifies it a lot!

what would be a healthy value to start off with?

Post #1339114

GilaMonster

Posted Friday, August 10, 2012 5:18 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

No way to answer that one. Higher than 384, but the higher you set it the more it cuts into the portion of the buffer pool under 2GB (which is used by the plan cache and all the other SQL caches other than data)

I strongly recommend you look at moving to 64 bit SQL as a solution. The mem to leave and restarts are a mitigation, nothing more.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1343298

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions