Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-06
5
Medium Priority
?
716 Views
Last Modified: 2012-05-05
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?

0
Comment
Question by:pvsadm
  • 2
  • 2
4 Comments
 

Author Comment

by:pvsadm
ID: 22893392
attached application eventlog of server
0
 
LVL 25

Accepted Solution

by:
Luis Pérez earned 2000 total points
ID: 22893722
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
 
LVL 25

Expert Comment

by:Luis Pérez
ID: 22893744
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
 

Author Comment

by:pvsadm
ID: 22893933
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

810 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