asked
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...
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
Yes, there are 2 instances on this server with memory allocated to them. how to enable "lock pages in memory" in SQL server 2005?
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?
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.
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.
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
@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.
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
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
1 Person is following this question.
