|
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 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 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 2011-08-29 05:49:34.99 spid55 Can someone help me?? Thanks in advance...
(comments are locked)
|
|
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?
Aug 22 '11 at 12:22 AM
Mandar Alawani
(comments are locked)
|
|
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
(comments are locked)
|
|
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.
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.
Aug 22 '11 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 '11 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 '11 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 '11 at 12:32 PM
Blackhawk-17
(comments are locked)
|
|
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
(comments are locked)
|
