memory problems resulting in 'not enough system memory to execute query' and unavailability of server

MEMORY PROBLEM MAIN DATABASE SERVER

We are experiencing frequently errors in the sqlserver error log:


Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1048576

11/04/2008 06:19:27,spid59,Unknown,MEMORYBROKER_FOR_RESERVE <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYBROKER_FOR_STEAL <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYBROKER_FOR_CACHE <nl/>
11/04/2008 06:19:27,spid59,Unknown,Big Gateway <nl/>
11/04/2008 06:19:27,spid59,Unknown,Medium Gateway <nl/>
11/04/2008 06:19:27,spid59,Unknown,Small Gateway <nl/>
11/04/2008 06:19:27,spid59,Unknown,Optimization Queue <nl/>
11/04/2008 06:19:27,spid59,Unknown,Small Query Memory Objects:  Grants=0 Waiting=0 Maximum=6732 Available=6732 Limit=6732
11/04/2008 06:19:27,spid59,Unknown,Query Memory Objects:  Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=134655
11/04/2008 06:19:27,spid59,Unknown,Query Memory Objects:  Grants=0 Waiting=0 Maximum=127923 Available=127923 Limit=127923
11/04/2008 06:19:27,spid59,Unknown,Global Memory Objects: <nl/>
11/04/2008 06:19:27,spid59,Unknown,Process physical/virtual memory pressure: 0/0 System physical memory pressure: 0
11/04/2008 06:19:27,spid59,Unknown,Procedure Cache:  TotalProcs=3 TotalPages=21 InUsePages=2
11/04/2008 06:19:27,spid59,Unknown,Buffer Counts:  Committed=178879 Target=179200 Hashed=173734<nl/>
11/04/2008 06:19:27,spid59,Unknown,Buffer Distribution:  Stolen=3042 Free=559 Cached=1544<nl/>
11/04/2008 06:19:27,spid59,Unknown,OBJECTSTORE_LOCK_MANAGER (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,OBJECTSTORE_SERVICE_BROKER (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,OBJECTSTORE_SNI_PACKET (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,OBJECTSTORE_LBSS (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,USERSTORE_SXC (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,USERSTORE_OBJPERM (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,USERSTORE_TOKENPERM (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,USERSTORE_DBMETADATA (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,USERSTORE_SCHEMAMGR (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_SYSTEMROWSET (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_EVENTS (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_BROKERTO (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_BROKERREADONLY (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_BROKERRSB (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_BROKERUSERCERTLOOKUP (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_BROKERDSH (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_BROKERKEK (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_BROKERTBLACS (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_STACKFRAMES (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_XMLDBATTRIBUTE (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_XMLDBELEMENT (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_XMLDBTYPE (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_VIEWDEFINITIONS (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_NOTIF (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_TEMPTABLES (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_XPROC (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_PHDR (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_SQLCP (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,CACHESTORE_OBJCP (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SOSNODE (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_HOST (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SQLXP (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_FULLTEXT (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SNI (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SQLHTTP (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SQLSERVICEBROKER (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SQLCLR (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SQLCONNECTIONPOOL (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SQLSTORENG (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SQLUTILITIES (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SQLOPTIMIZER (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SQLBUFFERPOOL (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,MEMORYCLERK_SQLGENERAL (Total) <nl/>
11/04/2008 06:19:27,spid59,Unknown,Memory node Id = 0 <nl/>
11/04/2008 06:19:27,spid59,Unknown,Memory Manager <nl/>
11/04/2008 06:19:27,spid59,Unknown,Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1048576




After this error we keep getting more and more sql server memory wait states.  
Resulting in more and more errors like There is insufficient system memory to run this query .

And at last the server is no more available for connections and must be rebooted.

Our server was up and running for nearly 300 days and this started unexpected&

Any solution?

pvsadmAsked:
Who is Participating?
 
Luis PérezConnect With a Mentor Software Architect in .NetCommented:
300 days running up... it's a HUGE number of days for a data server to be running up. You said: "Our server was up and running for nearly 300 days and this started unexpected". Surprised? It's a good practice to make a periodical reboot (tipically at hours of few client activity), because we all know that system's memory loads and has little leaks and does not clears itself properly. If there are no hours of few client activity, then it means that you need more than one server, for example a web-farm cluster architecture.
0
 
pvsadmAuthor Commented:
attached application eventlog of server
0
 
Luis PérezSoftware Architect in .NetCommented:
Anywhere, if this problem has appeared once, probably the cause has been the long number of days running. But if it repeats frecuently, then maybe one or more memory modules of your system are corrupt. Try replacing them (it's a low cost solution).
0
 
pvsadmAuthor Commented:
there are no inidcations that something is wrong with the hardware. but we will check.
the problem is repeating... not (yet) predictable...

(i try to attach the application eventlog... but is doesn't seem to work)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.