| Author |
Topic |
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
 Posted - 2010-11-03 : 14:44:09
|
| 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:[url]http://www.flickr.com/photos/7714646@N07/5143673700/sizes/l/in/photostream/ [/url]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
38200 Posts |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2010-11-03 : 15:19:41
|
| 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, |
 |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2010-11-03 : 15:40:16
|
| Can someone help please. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2010-11-03 : 16:05:50
|
| 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2010-11-04 : 10:21:10
|
| 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. |
|
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2010-11-04 : 11:36:37
|
| 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/ |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2010-11-04 : 19:16:47
|
| >>it really isn't ambiguous at all.I do hope this was meant tongue in cheek Russell.......... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-04 : 19:47:32
|
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  |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2010-11-04 : 21:16:41
|
| 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-11-04 : 21:26:47
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2010-11-05 : 08:45:42
|
| Thanks Tara. |
|
|
|
Johnf62
Starting Member
7 Posts |
Posted - 2013-05-08 : 05:40:29
|
| Thanks for this Tara it sorted my problem.John Frederick |
|
|
|
|
SQL 2008 Insufficient system memory in resource?