[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

sql server 2008r2 standard - internal memory error

Posted on 2016-09-18
13
Medium Priority
?
99 Views
Last Modified: 2016-11-28
running sql server 2008r2 standard SP3 - 8 GB memory max

One if our Applications is failing with this error: 
ERROR : (6784 | 6960) : (IS | INT_KOCPROD) : node01_KOCPROD : LM_36488 : Session task instance [s_DataSync_CNTCT_UPDT_MM2ING_STG_To_MQ] : [REP_12400 Repository Error (Microsoft SQL Server Native Client 11.0: There is insufficient system memory in resource pool 'internal' to run this query.
SQL State: 42000 Native Error: 701
State: 123 Severity: 17
SQL Server Message: There is insufficient system memory in resource pool 'internal' to run this query.


Database driver error...
Function Name : Execute
SQL Stmt : SELECT DISTINCT T.TASK_ID, T.SUBJECT_ID FROM OPB_TASK T, OPB_SESSION S, OPB_VALIDATE V, OPB_MAPPING M, OPB_WIDGET_INST I, OPB_SRC R, OPB_USER_GROUP U WHERE T.IS_VISIBLE = ? AND T.TASK_ID = S.SESSION_ID AND T.VERSION_NUMBER = S.VERSION_NUMBER AND T.TASK_ID IN (345) AND T.TASK_TYPE = 68 AND U.ID = ? AND U.TYPE = 1 AND S.MAPPING_ID = M.MAPPING_ID AND (M.IS_VISIBLE <> 2 OR (M.IS_VISIBLE = 2 AND M.CHECKOUT_USER_ID = U.ID)) AND I.MAPPING_ID = M.MAPPING_ID AND I.VERSION_NUMBER = M.VERSION_NUMBER AND I.WIDGET_TYPE = 1 AND R.SRC_ID = I.WIDGET_ID AND V.SUBJECT_ID = R.SUBJ_ID AND V.OBJECT_TYPE = I.WIDGET_TYPE AND V.OBJECT_ID = R.SRC_ID AND V.VERSION_NUMBER = R.VERSION_NUMBER AND V.INV_UTC > T.UTC_LAST_SAVED AND V.INV_TYPE = 1 AND T.IS_VALID = 1 AND (R.IS_VISIBLE <> 2 OR (R.IS_VISIBLE = 2 AND R.CHECKOUT_USER_ID = U.ID)) )]

Open in new window


In the sql server log we are getting the following error:
There is insufficient system memory in resource pool 'internal' to run this query.
Error: 701, Severity: 17, State: 123.

Full SQL server log attached.  Can anyone help?
kocsql01pdi.log
0
Comment
Question by:itsonlyme4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
13 Comments
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 41804007
You had quite a few failures there, including two within replication subsystems. SPID 161 looks like it attempted a whole series of operations (unsuccessfully)  without checking for errors after each one, or maybe it was checking but was coded to retry. It was still trying to go on in the last lines of the log segment.

There is a detailed trace for SPID 104 that doesn't mean a whole lot to me. The first failure logged was in SPID 182. Was that the actual beginning of the problem?

The query shown in the application error is lengthy, but it doesn't use JOIN syntax and that makes it look more complex than it really is. Nothing in it stands out to me that says it is the culprit; it seems more likely to be a victim.

Did anything change around the time this started happening?

Is there something that triggers the symptoms, and is the system OK the rest of the time? Does it recover the memory on its own?

Are all these SPIDs (other than perhaps the replication ones) spun off by one application operation, or are there multiple operations (and perhaps users) being affected?

Is CLR enabled and if so, is the application using it? Is the application using anything else "special" like extended stored procedures? Things that could run amok and use up all the memory?

Is this 64 bit SQL Server?

I am just trying to get some feeling for what might be going on.
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41804155
Is your query face this issue or even "SELECT TOP 100 * FROM a table" is also facing the same issue ?

Check sp_who2 and see which session is killing all memory

please let me know, Thnks
0
 

Author Comment

by:itsonlyme4
ID: 41804363
Is there something that triggers the symptoms, and is the system OK the rest of the time? Does it recover the memory on its own?
      The Application running the lengthy query runs a series of jobs every 15 minutes to do a data sync operation.  I agree that it is more likely a victim.  The job does not  fail every time it runs.. This is a 64 bit install of sql server .   I am assuming CLR is OFF as it is off by default.   We are  configured for 8GB max memory but there is 40GB memory available on the Server.  should I UP the memory max?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41804369
If your query is the Victim that you have to optimize your query. You can also check if something is blocking your query.

A quick fix will be update stats on your table and rebuild indexes used in your query
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41804618
@Author - Any update on this?
0
 
LVL 16

Accepted Solution

by:
Megan Brooks earned 2000 total points (awarded by participants)
ID: 41804867
If you have additional memory readily available then yes, I think you can learn something by increasing it, and it may even be that your application is written such that, with the current state of the database, you need more than 8 Gb. If you can get it to stop crashing this way (it's the queries that are crashing, rather than SQL Server itself, but that is small consolation), you may be able to see more clearly if there is a particular query or set of queries at the root of the problem. When you have cascading memory failures like this, it's hard to see anything.

Or if more memory fixes it and you are satisfied with that, maybe you are done.
0
 

Author Comment

by:itsonlyme4
ID: 41806832
no update.   we ended up recycling the instance yesterday and the problem corrected itself.   Still not sure if adding more memory is the answer as I do not yet have root cause.

before we rebooted yesterday,  we did see some processes with a very high  "Resource Semiphore" LastWaitType
0
 
LVL 16

Assisted Solution

by:Megan Brooks
Megan Brooks earned 2000 total points (awarded by participants)
ID: 41807074
Resource_Semaphore waits are a sign of memory pressure. No surprise there. When they first show up, however, that could be a good time to search for the cause, before processes start to die.
0
 

Author Comment

by:itsonlyme4
ID: 41807273
The processes died..  then we saw the Resource_Semaphore waits ..   If this is a sign of memory pressure then I would think that increasing the MAX memory sounds like the way to go.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41839893
We are  configured for 8GB max memory but there is 40GB memory available on the Server.
So what for you want those extra 40GB? A waste of money if you're not using it, right?
SQL Server as any other database management system (DBMS) is a resource eater, specially memory, even more than CPU, so everything that you can give to your SQL Server instance will only benefits the performance.
1
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41860157
itsonlyme4, a feedback will be appreciated.
Cheers
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41880526
itsonlyme4, did you end by increasing the max server memory for the SQL Server instance?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41903862
Recommendation is to close this question by accepting the above comments as solution.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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 …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

656 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