SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL 2008 Insufficient system memory in resource?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 11/03/2010 :  14:44:09  Show Profile  Reply with Quote
Hi All,

We have our application at a client server that the client use. There is an application server with Windows 2003 Server and a Database server with Windows 2008 server that have SQL Server 2008.

We have some schedule jobs (through windows task scheduler) on application server that runs some batch files every Friday to load and process the data into the application. These schedule jobs runs on application server every Friday evening and finish around Sunday afternoon. Today when I got on the server, the schedule job that ran on 10/22/2010 was still running until now which is not right. Our application logs tells an error on 10/22/2010 "There is insufficient system memory in resource pool 'internal' to run this query." On the database server when I look at the database (SQL Server 2008) logs it also have the same error on 10/22/2010 "There is insufficient system memory in resource pool 'internal' to run this query" another log on 10/22/2010 says "SQL Trace was stopped due to server shutdown. Trace ID='1'. This is an informational message only' no user action required."

You can also see the screenshot of the log at the following address:
http://www.flickr.com/photos/7714646@N07/5143673700/sizes/l/in/photostream/


Can someone explain me what is going on? In simple English what does it mean by the error "There is insufficient system memory in resource pool 'internal' to run this query"? I checked the internet but the explanation is too technical for me. I need to tell the client what heppened on thioer server and that is why the data did not process.

I can now kill the schedule jobs that re-run them but I don’t want the same error to happen again; what is this error and how I can prevent it happening again? Please help.

Zee

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/03/2010 :  14:47:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
They likely need to grant the "lock pages in memory" privilege to the SQL Server service account.

http://msdn.microsoft.com/en-us/library/ms190730.aspx



Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 11/03/2010 :  15:19:41  Show Profile  Reply with Quote
tkizer - Your link tells that locking pages in memory is not required on 64-bit operating system. And this database server is a 64 bit operating system. So please respond accordingly. Below is the operating System information for the servers.

Database Server (MS Windows Server 2008 R2 Standard - 64 Bit Operating System)

Application Server (MS Windows Server 2003 R2 Standard Edition with Service Pack 2 - 32 Bit Operating System)

Please help.

Thanks,
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 11/03/2010 :  15:40:16  Show Profile  Reply with Quote
Can someone help please.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/03/2010 :  15:41:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
Ignore what it says about 64-bit. It is absolutely needed on 64-bit. There is plenty of documentation out there to show this, so feel free to google it. The link I posted was to show you the procedure how to do it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 11/03/2010 :  16:05:50  Show Profile  Reply with Quote
tkizer,

Thanks for all your help on this. The issue is that the client also have our application on test enviornment (which is less powerful than production) with Windows Server 2003 as Operating Sysytem on both database and application servers; and just like production enviornment the "lock pages in memory" privilege is not enable there and there are no issues there what so ever; although both enviornments process the exact data. If I make this "lock pages in memory" privilege changes on production I need to tell client why I am doing that and why it did not effect the test enviornment? Can you please explain what should I be telling the client before I grant this "lock pages in memory" privilege?

Thanks a lot.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/03/2010 :  18:25:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
There is at least one MS KB article that explains it in detail. I believe your error message is mentioned in the KB article. Search MS's knowledge base for your error and see what you get.

The test environment may be processing the same data, but I doubt the load is the same. The load on the system makes a big difference in regards to hardware resources such as memory.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

patshaw
Posting Yak Master

177 Posts

Posted - 11/04/2010 :  10:21:10  Show Profile  Reply with Quote
Is that really true Tara? I know this is a subject which is ambiguous at best and the deeper you read into it the more inconclusive it becomes, but I have always gone with the notion that 'Lock Pages' is only required on 32-bit OS.
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 11/04/2010 :  11:36:37  Show Profile  Reply with Quote
tkizer,

When I run the following query it tells that it is not R2 Sql 2008 version.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
10.0.1600.22 RTM Standard Edition (64-bit)


So at this server what we have currently for SQL 2008 as it was released to manufacturing and nothing appear to be added to it. The following Microsoft Support article list the bugs (including this insufficient memory issue) to be fix by installing SQL Server 2008 SP2. I am advising my client to first install Service Pack 2 to see if this is going to resolve the insufficient memory issue.

http://support.microsoft.com/kb/2285068/
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 11/04/2010 :  14:07:09  Show Profile  Visit russell's Homepage  Reply with Quote
quote:
Originally posted by patshaw

Is that really true Tara? I know this is a subject which is ambiguous at best and the deeper you read into it the more inconclusive it becomes, but I have always gone with the notion that 'Lock Pages' is only required on 32-bit OS.



only required on 32x. still should be granted on 64x. it really isn't ambiguous at all.

http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx


Edited by - russell on 11/04/2010 14:08:02
Go to Top of Page

patshaw
Posting Yak Master

177 Posts

Posted - 11/04/2010 :  19:16:47  Show Profile  Reply with Quote
>>it really isn't ambiguous at all.

I do hope this was meant tongue in cheek Russell..........
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/04/2010 :  19:37:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
It is required on 32-bit, and highly recommended on 64-bit. So put it on your servers.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 11/04/2010 :  19:47:32  Show Profile  Visit russell's Homepage  Reply with Quote
quote:
Originally posted by patshaw

>>it really isn't ambiguous at all.

I do hope this was meant tongue in cheek Russell..........

It's not tongue in cheek. It isn't ambiguous. You should always grant lock pages in memory to the sql service account.


quote:
Originally posted by tkizer

It is required on 32-bit, and highly recommended on 64-bit. So put it on your servers.
Exactly

Edited by - russell on 11/04/2010 19:48:02
Go to Top of Page

patshaw
Posting Yak Master

177 Posts

Posted - 11/04/2010 :  21:16:41  Show Profile  Reply with Quote
Tara/Russell,

Your advice has been accepted but I have to disagree with you when you say that information on this subject is not ambiguous.

If you look across many articles online you get very conflicting information as to whether Lock Pages should be granted on 64-bit. If you take the 'Fun with Locked Pages, AWE, Task Manager, and the Working Set…' article as gospel (which perhaps it should be) then ok, it states you should do it but others say only grant it when the server is under severe memory pressure, others say don't use it at all on 64-bit. The fact that even Microsoft will only commit to saying that it is 'Not Required' on 64-bit servers makes this unclear at best. Even the afore mentioned artcle starts with "I realize that the topic of “locked pages’ and AWE can be confusing. I don’t blame anyone for being confused on this. I also realize we have blogged and talked about this topic many times perhaps “beating it to death”."

Does that not suggest much general ambiguity on this subject? Thanks for putting me right in this area though....I will apply your advice to my SQL Servers.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/04/2010 :  21:26:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
It's not ambiguous to me because I've heard it directly from high level Microsoft engineers.

If you are interested in hearing about it, open a PSS case with MS and let them know you've got a performance issue you need help with. One of the things that they are going to have you check is if the "lock pages in memory" is granted to the SQL Server service account. They'll have you do this regardless of x64 or x86. And feel free to ask them questions. I certainly did.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

patshaw
Posting Yak Master

177 Posts

Posted - 11/05/2010 :  08:45:42  Show Profile  Reply with Quote
Thanks Tara.
Go to Top of Page

Johnf62
Starting Member

United Kingdom
7 Posts

Posted - 05/08/2013 :  05:40:29  Show Profile  Reply with Quote
Thanks for this Tara it sorted my problem.

John Frederick
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000