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

Posted on 2008-11-06
Last Modified: 2012-05-05

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?

Question by:pvsadm

    Author Comment

    attached application eventlog of server
    LVL 25

    Accepted Solution

    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.
    LVL 25

    Expert Comment

    by:Luis Pérez
    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).

    Author Comment

    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)

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now