报错是在3个小时前的，但是问题还没有立即出现，3个小时左右，windows系统 *** 作总是出错，数据库连接不上。sqlserver代理总是自动停止，启动几分钟就停止。
部分错误如下：
--查看日志是否报内存信息exec xp_readerrorlog 1,1,'Memory',NulL,'2015-01-23 13:00:00','2015-01-23 16:20:00','ASC' exec xp_readerrorlog 1,'2015-01-23 13:34:00','2015-01-23 13:34:30','ASC'
看到有内存输出情况！
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536 Memory Manager KB ---------------------------------------- ---------- VM Reserved 76360868 VM Committed 64701408 Locked Pages Allocated 0 Reserved Memory 1024 Reserved Memory In Use 0 Memory node ID = 0 KB ---------------------------------------- ---------- VM Reserved 76357220 VM Committed 64697872 Locked Pages Allocated 0 MultiPage Allocator 1621392 SinglePage Allocator 1686352 Memory node ID = 64 KB ---------------------------------------- ---------- VM Reserved 2560 VM Committed 2504 Locked Pages Allocated 0 MultiPage Allocator 2416 SinglePage Allocator 1686352 MEMORYCLERK_sqlGENERAL (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16184 MultiPage Allocator 6928 MEMORYCLERK_sqlBUFFERPOol (node 0) KB ---------------------------------------- ---------- VM Reserved 67158016 VM Committed 62676608 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 912 MEMORYCLERK_sqlqueryEXEC (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 416 MultiPage Allocator 0 MEMORYCLERK_sqlOPTIMIZER (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 768 MultiPage Allocator 896 MEMORYCLERK_sqlUTIliTIES (node 0) KB ---------------------------------------- ---------- VM Reserved 840 VM Committed 840 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 136 MultiPage Allocator 0 MEMORYCLERK_sqlSTORENG (node 0) KB ---------------------------------------- ---------- VM Reserved 14016 VM Committed 14016 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 24520 MultiPage Allocator 5176 MEMORYCLERK_sqlCONNECTIONPOol (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 32424 MultiPage Allocator 0 MEMORYCLERK_sqlCLR (node 0) KB ---------------------------------------- ---------- VM Reserved 6314812 VM Committed 108128 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 1488 MultiPage Allocator 10448 MEMORYCLERK_sqlSERVICEbroKER (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 152 MultiPage Allocator 544 MEMORYCLERK_sqlhttp (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 MEMORYCLERK_SNI (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3984 MultiPage Allocator 16 MEMORYCLERK_SNI (node 64) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 16 MEMORYCLERK_SNI (Total) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3984 MultiPage Allocator 32 MEMORYCLERK_FulLTEXT (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 24 MultiPage Allocator 0 MEMORYCLERK_sqlXP (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 MEMORYCLERK_BHF (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 1200 MultiPage Allocator 0 MEMORYCLERK_sqlQERESERVATIONS (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 960 MultiPage Allocator 0 MEMORYCLERK_XE_BUFFER (node 0) KB ---------------------------------------- ---------- VM Reserved 4224 VM Committed 4224 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 0 MEMORYCLERK_HOST (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 24 MultiPage Allocator 0 MEMORYCLERK_SOSNODE (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 106432 MultiPage Allocator 13832 MEMORYCLERK_SOSNODE (node 64) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 2336 MEMORYCLERK_SOSNODE (Total) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 106440 MultiPage Allocator 16168 MEMORYCLERK_FulLTEXT_SHMEM (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 96 SM Committed 96 SinglePage Allocator 0 MultiPage Allocator 0 MEMORYCLERK_sqlSERVICEbroKERTRANSPORT (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 48 MultiPage Allocator 0 MEMORYCLERK_XE (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 4024 MultiPage Allocator 320 CACHESTORE_OBJCP (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 412192 MultiPage Allocator 15600 CACHESTORE_sqlCP (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 542632 MultiPage Allocator 36624 CACHESTORE_PHDR (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 19912 MultiPage Allocator 0 CACHESTORE_XPROC (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 112 MultiPage Allocator 0 CACHESTORE_TEMPtableS (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 168 MultiPage Allocator 0 CACHESTORE_NOTIF (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_VIEWDEFinitioNS (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_XMLDBTYPE (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 32 MultiPage Allocator 0 CACHESTORE_XMLDBELEMENT (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_XMLDBATTRIBUTE (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_STACKFRAMES (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 8 CACHESTORE_STACKFRAMES (node 64) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 8 CACHESTORE_STACKFRAMES (Total) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 16 CACHESTORE_broKERTBLACS (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 56 MultiPage Allocator 0 CACHESTORE_broKERKEK (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_broKERDSH (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_broKERUSERCERTLOOKUP (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_broKERRSB (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_broKERReadonly (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 32 MultiPage Allocator 0 CACHESTORE_broKERTO (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_EVENTS (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_CLRPROC (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 48 MultiPage Allocator 0 CACHESTORE_SYstemROWSET (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3944 MultiPage Allocator 0 CACHESTORE_CONVPRI (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 64 MultiPage Allocator 0 CACHESTORE_FulLTEXTStopList (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 32 MultiPage Allocator 0 USERSTORE_SCHEMAMGR (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 73512 MultiPage Allocator 0 USERSTORE_DB@R_502_6135@DATA (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 13352 MultiPage Allocator 0 USERSTORE_TOKENPERM (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 226488 MultiPage Allocator 1522896 USERSTORE_OBJPERM (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3392 MultiPage Allocator 32 USERSTORE_SXC (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 15864 MultiPage Allocator 0 USERSTORE_SXC (node 64) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 USERSTORE_SXC (Total) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 15872 MultiPage Allocator 0 OBJECTSTORE_LBSS (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 240 MultiPage Allocator 6288 OBJECTSTORE_SNI_PACKET (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 54240 MultiPage Allocator 56 OBJECTSTORE_SNI_PACKET (node 64) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 56 OBJECTSTORE_SNI_PACKET (Total) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 54240 MultiPage Allocator 112 OBJECTSTORE_SERVICE_broKER (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 424 MultiPage Allocator 0 OBJECTSTORE_LOCK_MANAGER (node 0) KB ---------------------------------------- ---------- VM Reserved 262144 VM Committed 262144 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 127616 MultiPage Allocator 0 OBJECTSTORE_LOCK_MANAGER (node 64) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 24 MultiPage Allocator 0 OBJECTSTORE_LOCK_MANAGER (Total) KB ---------------------------------------- ---------- VM Reserved 262144 VM Committed 262144 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 127640 MultiPage Allocator 0 OBJECTSTORE_SECAUDIT_EVENT_BUFFER (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 Buffer Pool Value ---------------------------------------- ---------- Committed 7584275 Target 7584275 Database 7373009 Dirty 15408 In IO 0 Latched 1 Free 456 Stolen 210810 Reserved 160 Visible 7584275 Stolen Potential 6994699 limiting Factor 18 Last OOM Factor 0 Page life Expectancy 7012 Process/System Counts Value ---------------------------------------- ---------- Available Physical Memory 439861248 Available Virtual Memory 8717305466880 Available Paging file 5242880 Working Set 64049631232 Percent of Committed Memory in WS 96 Page Faults 50018252 System physical memory high 1 System physical memory low 0 Process physical memory low 0 Process virtual memory low 0 Procedure Cache Value ---------------------------------------- ---------- TotalProcs 10926 TotalPages 128391 InUsePages 663 Global Memory Objects Pages ---------------------------------------- ---------- Resource 1086 Locks 15955 XDES 2271 SETLS 4 SE Dataset Allocators 8 SubpDesc Allocators 4 SE SchemaManager 2209 SE Column @R_502_6135@data Cache 6979 sqlCache 1896 Replication 1395 ServerGlobal 51 XP Global 2 Sorttables 35 query Memory Objects (internal) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 5211490 Current Max 5211490 Future Max 5211490 Physical Max 5686510 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small query Memory Objects (internal) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 12800 Current Max 12800 Future Max 12800 query Memory Objects (default) Value ---------------------------------------- ---------- Grants 4 Waiting 0 Available 5686350 Current Max 5686510 Future Max 5686510 Physical Max 5686510 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small query Memory Objects (default) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 12800 Current Max 12800 Future Max 12800 Optimization Queue (internal) Value ---------------------------------------- ---------- Overall Memory 49801379840 Target Memory 45764108288 Last Notification 1 Timeout 6 Early Termination Factor 5 Small Gateway (internal) Value ---------------------------------------- ---------- Configured Units 16 Available Units 16 Acquires 0 Waiters 0 Threshold Factor 380000 Threshold 380000 Medium Gateway (internal) Value ---------------------------------------- ---------- Configured Units 4 Available Units 4 Acquires 0 Waiters 0 Threshold Factor 12 Threshold -1 Big Gateway (internal) Value ---------------------------------------- ---------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 Threshold -1 Optimization Queue (default) Value ---------------------------------------- ---------- Overall Memory 49801379840 Target Memory 45734273024 Last Notification 1 Timeout 6 Early Termination Factor 5 Small Gateway (default) Value ---------------------------------------- ---------- Configured Units 16 Available Units 16 Acquires 0 Waiters 0 Threshold Factor 380000 Threshold 380000 Medium Gateway (default) Value ---------------------------------------- ---------- Configured Units 4 Available Units 4 Acquires 0 Waiters 0 Threshold Factor 12 Threshold -1 Big Gateway (default) Value ---------------------------------------- ---------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 Threshold -1 Memory Pool Manager Pages ---------------------------------------- ---------- Reserved Current 160 Reserved limit 6994264 Memory Pool (internal) Pages ---------------------------------------- ---------- Allocations 53736 Predicted 53736 Private Target 0 Private limit 0 Total Target 7205061 Total limit 7205061 OOM Count 0 MEMORYbroKER_FOR_CACHE (internal) Pages ---------------------------------------- ---------- Allocations 39888 Rate 0 Target Allocations 5612479 Future Allocations 0 Overall 6079270 Last Notification 1 MEMORYbroKER_FOR_Steal (internal) Pages ---------------------------------------- ---------- Allocations 13848 Rate -23 Target Allocations 5586439 Future Allocations 0 Overall 6079270 Last Notification 1 MEMORYbroKER_FOR_RESERVE (internal) Pages ---------------------------------------- ---------- Allocations 0 Rate 0 Target Allocations 5572591 Future Allocations 0 Overall 6079270 Last Notification 1 Memory Pool (default) Pages ---------------------------------------- ---------- Allocations 157181 Predicted 1578734 Private Target 0 Private limit 0 Total Target 7205061 Total limit 7205061 OOM Count 0 MEMORYbroKER_FOR_CACHE (default) Pages ---------------------------------------- ---------- Allocations 146897 Rate 4 Target Allocations 5719492 Future Allocations 0 Overall 6079270 Last Notification 1 MEMORYbroKER_FOR_Steal (default) Pages ---------------------------------------- ---------- Allocations 10164 Rate 42 Target Allocations 5582797 Future Allocations 0 Overall 6079270 Last Notification 1 MEMORYbroKER_FOR_RESERVE (default) Pages ---------------------------------------- ---------- Allocations 120 Rate -492 Target Allocations 6079270 Future Allocations 1421627 Overall 6079270 Last Notification 1
比较多，其实就相当于以下的两个语句的查询结果：
SELECT type,SUM(virtual_memory_reserved_kb) AS [vm reserved],SUM(virtual_memory_committed_kb) AS [vm commited],SUM(awe_allocated_kb) AS [awe allocated],SUM(shared_memory_reserved_kb) AS [sm reserved],SUM(shared_memory_committed_kb) AS [sm committed],SUM(single_pages_kb) AS [Stolen in Buffer Pool],SUM(multi_pages_kb) AS [MemToleave],SUM(single_pages_kb) + SUM(multi_pages_kb) AS [Stolen],SUM(virtual_memory_committed_kb) + SUM(multi_pages_kb) AS [Buffer Pool]FROM sys.dm_os_memory_clerksGROUP BY typeORDER BY typedbcc memorystatus
先看输出头信息：
Failed VirtualAllocateBytes:FAIL_VIRTUAL_RESERVE 65536
这被认为Memtoleave 不足的情况。
对以下两种情况，通常认为buffer pool内的stolen不足，即single_pages不足。
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE
Failed allocatepages:FAIL_PAGE_ALLOCATION
再看当时在数据库运行查询时的错误：
消息701,级别 17,状态 123,第 2 行资源池 'default'没有足够的系统内存来运行此查询
消息701，也表明 Stolen 内存有压力。
现在先了解什么是Stolen ，multi_pages，Memtoleave
Stolen ：
对于内存的请求访问，sqlserver大部分是先是reserved（预留）一块大的内存，再往内存中一小块一小块地commit（提交），这样可以很好地管理物理内存的访问，然而也有少部分数据，不先reserved，不管有没有地址空间，都直接提交，这就是所谓的 [偷] ，在sqlserver里叫 【stolen】
stolen 通常不会太大,SQL语句的执行过程都需要用到.除非游标未关闭或者内存溢出才增大.
multi_pages，Memtoleave ：
sqlserver数据库通常以8KB的数据进行内存的请求访问，和数据页大小一致，可更有效地管理（大部分存在buffer pool中）。对于大于8KB的内存申请，sqlserver把它们集中在另外一个区域，成为【multi-pages】或者【Memtoleave 】，这种分配也成为 multi-pages allocation。
哪些内存申请会使用到 multi-pages：
连接：network package size 4kb(默认)，存放buffer pool； 为8kb存放 multi-page（sp_configure 可设置）
大部分内存：锁数据结构、事务上下文、表格和索引元数据 以8k为单位，如编译和范式化大于8K，使用 multi-page
第三方代码：sqlserver不知道申请多少，都存放multi-page。
（Extended Procedures,COM objects (olE automation calls),Linked Servers,olEDB provIDers,sql CLR,sql Mail）
线程内存：每个以512kb为单位，存放 multi-page
各种内存分类方法之间的关系：
| 类型 | Database Cache | consumer | third Part Code | Threads |
| Reserved/Commit | 是 | 一般不是 | 一般不是 | 不是 |
| Stolen | 不是 | 是 | 是 | 是 |
| BufferPool( Single page) | 所有 | 绝大部分 | 没有 | 没有 |
| MemToleave( Multi-page) | 没有 | 一小部分 | 所有 | 所有 |
上面问题初步分析可能是是：
1. 第三方代码或者线程以直接提交的方式申请内存导致的内存溢出。
2. 或者是：游标未关闭；某个查询消耗大量内存
当前服务器情况分析：
64位windows2003安装64位sqlserver2008，MemToleave 上限默认不再限制
已设置max server memory，留4G给windows使用
线程限制为1024
buffer pool 大小 = (sql physical memory) - (Multi-page) ，并且不能大于 max server memory
当时观察系统available memory还有1G多可用，但是各种 *** 作大部分都不行。
看起来内存足够，主要是因为sqlserver设置了Max Server Memory，不能对外申请 。
这样提示内存不足不是sqlserver不断申请windows当前的可以内存，而是sqlserver内部的竞争。
既然 Max Server Memory 已经设置了上限，即 buffer pool 的上限。
是 Multi-page 增大导致buffer pool 的占用的内存被挤掉？
也有一种说法是，频繁的备份log造成的MemToleave碎片化问题导致内存不足。而当前在日志中输出这些内存信息的前一步，就是日志备份。日志是每小时备份本地一次，其他也有2个数据库也是一样，难道会是日志备份导致？
当时没有查询详细查看各内存对象和内存区域大小情况，从发生错误提示到问题现象的3个小时左右，都还能进行访问，然后逐渐不能访问。
当时还是先让服务器跑起来，所以直接重启了sqlserver服务 解决！
没有过多的信息，现在只有日志记录的一些信息，还没仔细查看，只把当时的内存对象申请内存情况拷贝出来。
上面的这些内存对象信息还不太了解具体情况和分析方法 。
也希望了解的大神指导一下哈 ~~ ^ o ^
内存溢出相关：
资源池 'default' 没有足够的系统内存来运行此查询
insufficient system memory in resource pool 'default'
How to find who is using / eating up the Virtual Address Space on your SQL Serve
SQL Server 内存泄露”(memory leak)的案例—游标导致的内存问题
How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005
SQL Server & memory leak: Are you sure?
SQL 2008执行语句遇到内存不足的案例(error 701)之一
SQL Server备份读写揭秘
BACKUP (Transact-SQL)
总结以上是内存溢出为你收集整理的SQLServer 内存溢出问题？（待解决）全部内容，希望文章能够帮你解决SQLServer 内存溢出问题？（待解决）所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）