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:
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?
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