I'd appreciate some help with the following, I've done some Googling, but haven't managed to resolve this one.
I keep receiving a message in the SQL Log 'Failed allocate pages: FAIL_PAGE_ALLOCATION 540', followed by a dump which I'll add to the end of this question.
For your information, the build is:
- Virtual Server running on vCenter
- Windows Server 2008 R2 64bit SQL
- Server 2012 SP1 Enterprise (11.0.2100) 8GB RAM SQL Server Max and Min memory is set to 6144 MB
The server is part of a 2 node Availability Group, this is the current primary node.
This instance is hosting the databases for Microsoft SharePoint
I ran a Perfmon trace for 24 hours which only revealed PLE dropping after one of the dumps, and then rising back up. Nothing else particularly odd.
SQL Server is still running.
EXTRACT FROM SQL LOG:
10/03/2014 09:59:52,spid866,Unknown,CACHESTORE_XMLDBELEMENT (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 8
10/03/2014 09:59:52,spid866,Unknown,CACHESTORE_XMLDBTYPE (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 8
10/03/2014 09:59:52,spid866,Unknown,CACHESTORE_VIEWDEFINITIONS (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 16
10/03/2014 09:59:52,spid866,Unknown,CACHESTORE_NOTIF (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 16
10/03/2014 09:59:52,spid866,Unknown,CACHESTORE_TEMPTABLES (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 448
10/03/2014 09:59:52,spid866,Unknown,CACHESTORE_XPROC (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 120
10/03/2014 09:59:52,spid866,Unknown,CACHESTORE_PHDR (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 157688
10/03/2014 09:59:52,spid866,Unknown,CACHESTORE_SQLCP (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 886856
10/03/2014 09:59:52,spid866,Unknown,CACHESTORE_OBJCP (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 409760
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_FSCHUNKER (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 48
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_LWC (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 8
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLLOGPOOL (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 8208
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_HADR (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 688
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_XE (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 218176
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_FILETABLE (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 16
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 232
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 24<nl/>SM Committed 24<nl/>Pages Allocated 0
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SOSMEMMANAGER (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 474880<nl/>VM Committed 474736<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 0
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SOSOS (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 192
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SOSNODE (Total) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 71968
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SOSNODE (node 64) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 2432
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SOSNODE (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 69536
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_HOST (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 24
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_XTP (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 16
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_XE_BUFFER (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 206208<nl/>VM Committed 206208<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 0
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLQERESERVATIONS (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 1328
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_BHF (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 192
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLXP (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 16
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_FULLTEXT (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 296
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SNI (Total) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 1024
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SNI (node 64) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 16
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SNI (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 1008
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLHTTP (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 8
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLSERVICEBROKER (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 984
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLCLR (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 1603008<nl/>VM Committed 28096<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 4816
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 20464
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLSTORENG (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 23040<nl/>VM Committed 23040<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 40144
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLUTILITIES (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 408
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLOPTIMIZER (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 2224
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLQUERYEXEC (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 2856
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLBUFFERPOOL (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 3251552<nl/>VM Committed 137416<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 3188808
10/03/2014 09:59:52,spid866,Unknown,MEMORYCLERK_SQLGENERAL (node 0) KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 0<nl/>Locked Pages Allocated 0<nl/>SM Reserved 0<nl/>SM Committed 0<nl/>Pages Allocated 23920
10/03/2014 09:59:52,spid866,Unknown,Memory node Id = 64 KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 0<nl/>VM Committed 20<nl/>Locked Pages Allocated 0
10/03/2014 09:59:52,spid866,Unknown,Memory node Id = 0 KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 274536504<nl/>VM Committed 6291428<nl/>Locked Pages Allocated 0<nl/>Pages Allocated 5393336<nl/>Pages Free 10504<nl/>Target Committed 6291432<nl/>Current Committed 6291432<nl/>Foreign Committed 0<nl/>Away Committed 0
10/03/2014 09:59:52,spid866,Unknown,Memory Manager KB<nl/>---------------------------------------- ----------<nl/>VM Reserved 274536568<nl/>VM Committed 6291448<nl/>Locked Pages Allocated 0<nl/>Large Pages Allocated 0<nl/>Emergency Memory 1024<nl/>Emergency Memory In Use 16<nl/>Target Committed 6291456<nl/>Current Committed 6291448<nl/>Pages Allocated 5393336<nl/>Pages Reserved 1224<nl/>Pages Free 10504<nl/>Pages In Use 3093536<nl/>Page Alloc Potential 2883352<nl/>NUMA Growth Phase 2<nl/>Last OOM Factor 2<nl/>Last OS Error 0
10/03/2014 09:59:52,spid866,Unknown,Process/System Counts Value<nl/>---------------------------------------- ----------<nl/>Available Physical Memory 787546112<nl/>Available Virtual Memory 8514575921152<nl/>Available Paging File 8859074560<nl/>Working Set 6637367296<nl/>Percent of Committed Memory in WS 100<nl/>Page Faults 256980685<nl/>System physical memory high 1<nl/>System physical memory low 0<nl/>Process physical memory low 0<nl/>Process virtual memory low 0
10/03/2014 09:59:52,spid866,Unknown,Failed allocate pages: FAIL_PAGE_ALLOCATION 540
Best Answer
Because there are only 8208 pages allocated to MEMORYCLERK_SQLLOGPOOL in the excerpt not sure kb2769594 is the culprit/fix. Also no reference to internal pool or HADR as in the kb2769594 example. https://support.microsoft.com/kb/2769594
So I think culprit/fix may be something else.
Surprising that no large pages allocated at all - I'd expect some for non-bpool with 8 gb of RAM on the server. SQL Server Enterprise edition? Low system memory is somewhat suprising - is there another large memory consumer beside SQL Server? Which OS version? Is the NTFS file cache well under control?
edit Via twitter also asked if 'lock pages in memory' LPIM is expected - no locked pages indicated above.