x

Insufficient memory error

Hi,

I receive the below error when the Server reboots in the SQL server 2005 Logs:

--Failed allocate pages: FAIL_PAGE_ALLOCATION 1

--There is insufficient system memory to run this query. Error: 701, Severity: 17, State: 193.

I have read about this solution: http://support.microsoft.com/kb/912439 but I think it does not apply as this is happening at SQL Server start itself and not during any query execution.

Also, this server is already on SP2.

I have allocated 256 MB to the SQL server, when I started receiving this error (Earlier 128MB was alllocated to SQL Server out of total of 4GB). There is 1 more instance on this server which has 750MB allocated to it and no such errors on that server.

Can someone tell me any other likely causes/resolutions for this? Or should I just allocate some more memory to the SQL server?

Thanks for any help...

I am afraid this error is still NOT GONE...I still get Insufficient memory errors couple of hours after server reboot. I have allocated 750MB max memory to the SQL instance, which is equal to the other instance.

Below is a part of the SQL error log:

2011-08-29 03:24:53.18 spid4s Recovery is complete. This is an informational message only. No user action is required. 2011-08-29 03:24:53.18 spid12s The Service Broker protocol transport is disabled or not configured. 2011-08-29 03:24:53.18 spid12s The Database Mirroring protocol transport is disabled or not configured. 2011-08-29 03:24:53.23 spid12s Service Broker manager has started. 2011-08-29 03:25:07.03 spid52 Using 'xpsqlbot.dll' version '2005.90.3042' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required. 2011-08-29 03:25:08.06 spid52 Using 'xpstar90.dll' version '2005.90.3042' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required. 2011-08-29 03:25:08.37 spid52 Using 'xplog70.dll' version '2005.90.3042' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required. 2011-08-29 03:29:48.18 spid13s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required. 2011-08-29 05:49:34.98 spid55
Memory Manager VM Reserved = 1631196 KB VM Committed = 792360 KB AWE Allocated = 0 KB Reserved Memory = 1024 KB Reserved Memory In Use = 0 KB

2011-08-29 05:49:34.98 spid20s Failed allocate pages: FAIL_PAGE_ALLOCATION 1 2011-08-29 05:49:34.98 spid54 Error: 701, Severity: 17, State: 193. 2011-08-29 05:49:34.98 spid54 There is insufficient system memory to run this query. 2011-08-29 05:49:34.99 spid55
Memory node Id = 0 VM Reserved = 1627036 KB VM Committed = 788352 KB AWE Allocated = 0 KB SinglePage Allocator = 729664 KB MultiPage Allocator = 9256 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SQLGENERAL (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 11832 KB MultiPage Allocator = 2240 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SQLBUFFERPOOL (Total) VM Reserved = 1614060 KB VM Committed = 776152 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 0 KB MultiPage Allocator = 392 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SQLOPTIMIZER (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 128 KB MultiPage Allocator = 88 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SQLUTILITIES (Total) VM Reserved = 240 KB VM Committed = 240 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 72 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SQLSTORENG (Total) VM Reserved = 512 KB VM Committed = 512 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 4136 KB MultiPage Allocator = 56 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SQLCONNECTIONPOOL (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 2528 KB MultiPage Allocator = 88 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SQLCLR (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SQLSERVICEBROKER (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 56 KB MultiPage Allocator = 192 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SQLHTTP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SNI (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 16 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_FULLTEXT (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SQLXP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_BHF (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 24 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_HOST (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SOSNODE (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16824 KB MultiPage Allocator = 5976 KB

2011-08-29 05:49:34.99 spid55
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 24 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_OBJCP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_SQLCP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 675608 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_PHDR (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_XPROC (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_TEMPTABLES (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_NOTIF (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_VIEWDEFINITIONS (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_XMLDBTYPE (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_XMLDBELEMENT (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_XMLDBATTRIBUTE (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_STACKFRAMES (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 0 KB MultiPage Allocator = 8 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_BROKERTBLACS (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 88 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_BROKERKEK (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB

2011-08-29 05:49:34.99 spid55
CACHESTORE_BROKERDSH (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB

MultiPage Allocator = 0 KB

Can someone help me??

Thanks in advance...
more ▼

asked Aug 21, 2011 at 10:34 PM in Default

Mandar Alawani gravatar image

Mandar Alawani
376 31 36 41

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Are you running multiple instance on the same server? Is the memory already allocated to the other instances? Are you using lock pages in memory? I you can answer yes on all these questions, then you have found the problem.

If the sum of max memory for the two instances (with lock pages in memory assigned) is more than the physical/availible memory then the second instance doesn't start.

The proper solution would be add more memory to handle both instances and the server OS and make sure the sum of max memory is less then (physical memory - OS memory (at least 2Gb)

If you want to disable lock pages in memory to be able to handle dynamic allocation of memory between the instances, you need to remove the SQL service account from the "Lock pages in memory" rights. Read more at: http://msdn.microsoft.com/en-us/library/ms190730.aspx
more ▼

answered Aug 22, 2011 at 12:13 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

Yes, there are 2 instances on this server with memory allocated to them. how to enable "lock pages in memory" in SQL server 2005?
Aug 22, 2011 at 12:22 AM Mandar Alawani
(comments are locked)
10|1200 characters needed characters left

Try disabling one of the SQL Server services and restarting your system. If the errors go away then change the SQL Server that is stopped and the one that is automatic and restart again.

If both of these start without the error then your system is unable to support both instances - perhaps there is another service that is taking all the RAM - Exchange?AV?etc etc?

If none of these can be removed then you need to get better hardware. Running a SQL instance on 256MB wont perform very well even if you can get rid of the error
more ▼

answered Aug 22, 2011 at 03:57 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

(comments are locked)
10|1200 characters needed characters left

From the error message it seems, that all the memory was consumed and SQL server cannot allocate additional memory required to execute a query.

What is your overall memory status when you receive the error? What is real memory allocation and free memory when you receive this error.

You wrote: such errors on that server.

I have allocated 256 MB to the SQL server, when I started receiving this error (Earlier 128MB was alllocated to SQL Server out of total of 4GB). There is 1 more instance on this server which has 750MB allocated to it and no such errors on that server.

How you have allocated the memory? You have set Minimum instance memory or Maximum instance memory?

Can you provide more detail about the memory settings for those instances and memory allocation in time when the problem occurs?
more ▼

answered Aug 22, 2011 at 01:50 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

I have set max memory allocated to both instances: 1st instance: min 128 max 256 MB 2nd instance: min 128 max 750MB

And I get the errors mentioned for the 1st instance. Earlier I there was a maximum of 128MB allocated to this instance.
Aug 22, 2011 at 02:46 AM Mandar Alawani

Max allocated memory doesn't mean, that server will be able to allocate that amount. If there is no available memory (which can be consumed by other instance, system and other application) then SQL server will be unable to allocate the memory even if it doesn't reach the 256 MB. So please check the free memory and amount of memory allocated by other applications.

If you want your SQL server service to have at least a particular memory available before it is consumed by other applications and services, you have to set the Minimum Memory setting.
Aug 22, 2011 at 04:05 AM Pavel Pawlowski
agreed, either the Max is set too low to let SQL start or the Min is too high in comparison with actual resources on the server
Aug 22, 2011 at 07:27 AM Fatherjack ♦♦
@Pavel Pawlowski - MIN is the lowest RAM will be reduced to for the instance assuming SQL Server had been able to get that much in the first place, it is not a guaranteed start-up amount.
Sep 07, 2011 at 12:32 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

I too facing the same issue. Configuration is as follow; O/S 32 bit Enterprise Editon SQL Server 2005 Enterprise 32 bit Physical Memory 10GB AWE Enabled LPM enabled Min SQL Memroy = 1024 MB Max SQL Memory - 7168

Plesae advise if you have a Fix for the below

/* spid52 Failed allocate pages: FAIL_PAGE_ALLOCATION 1 spid66 Error: 701, Severity: 17, State: 123. spid66 There is insufficient system memory to run this query. */

CACHESTORE_PHDR (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 693904 KB <--- Over 670MB MultiPage Allocator = 16 KB

more ▼

answered Jun 26, 2012 at 01:53 PM

dsharma37 gravatar image

dsharma37
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x56
x2

asked: Aug 21, 2011 at 10:34 PM

Seen: 4001 times

Last Updated: Jun 26, 2012 at 01:53 PM