none
数据库出错:系统内存不足,无法运行此查询。 RRS feed

  • 问题

  • 机器:HP580G5  16个CPU  32G内存    WIN2003 企业版32位   SQL2005企业版32位,使用AWE内存,分配了30G给SQLSERVER
    目前系统总使用内存:27。1G
    下午15:36  17:25 两次  监控程序发现数据库出错,查询错误日志如下:
    LogDate ProcessInfo Text
    2009-11-19 17:36:33.850 spid1s Worktable with partition ID 0 was dropped successfully after repeated attempts.
    2009-11-19 17:36:29.050 spid166   MEMORYBROKER_FOR_RESERVE    Allocations = 63936    Rate = -2048    Target Allocations = 60047    Future Allocations = 29052    Last Notification = SHRINK 
    2009-11-19 17:36:29.050 spid166   MEMORYBROKER_FOR_STEAL    Allocations = 23463    Rate = -1558    Target Allocations = 21253    Future Allocations = 0    Last Notification = SHRINK 
    2009-11-19 17:36:29.050 spid166   MEMORYBROKER_FOR_CACHE    Allocations = 69709    Rate = -19011    Target Allocations = 49190    Future Allocations = 0    Last Notification = SHRINK 
    2009-11-19 17:36:29.050 spid166   Big Gateway    Configured Units = 1 Available Units = 1 Acquires = 0    Waiters = 0 Threshold Factor = 8 Threshold = -1
    2009-11-19 17:36:29.050 spid166   Medium Gateway    Configured Units = 8 Available Units = 8 Acquires = 0    Waiters = 0 Threshold Factor = 12 Threshold = 14508714
    2009-11-19 17:36:29.050 spid166   Small Gateway    Configured Units = 32 Available Units = 31 Acquires = 1    Waiters = 0 Threshold Factor = 250000 Threshold = 250000
    2009-11-19 17:36:29.050 spid166   Optimization Queue    Overall Memory = 1068982272 Target Memory = 174104576 Last Notification = SHRINK Timeout = 6 Early Termination Factor = 5
    2009-11-19 17:36:29.050 spid166 Small Query Memory Objects:  Grants=0 Waiting=0 Maximum=2814 Available=2814 Limit=2814
    2009-11-19 17:36:29.050 spid166 Query Memory Objects:  Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=56280
    2009-11-19 17:36:29.050 spid166 Query Memory Objects:  Grants=6 Waiting=0 Maximum=53466 Available=11482 Limit=53466
    2009-11-19 17:36:29.050 spid166 Global Memory Objects:     Resource= 247    Locks= 203    SE Schema Mgr= 730    SQLCache= 1218    Replication= 2    ServerGlobal= 26    XPGlobal= 2    Xact= 403    SETLS= 16    DatasetMemObjs= 32    SubpDescPmos= 43    SortTables= 35
    2009-11-19 17:36:29.050 spid166 Process physical/virtual memory pressure: 1/0 System physical memory pressure: 0
    2009-11-19 17:36:29.050 spid166 Procedure Cache:  TotalProcs=10445 TotalPages=69283 InUsePages=1754
    2009-11-19 17:36:29.050 spid166 Buffer Counts:  Committed=3465216 Target=3465216 Hashed=3348530    Internal Reservation=32563 External Reservation=36475    Stolen Potential=25569     Min Free=1616 Visible=162816     Available Paging File=6928302080
    2009-11-19 17:36:29.050 spid166 Buffer Distribution:  Stolen=22427 Free=23994 Cached=70280    Database (clean)=3289524 Database (dirty)=58970    I/O=2, Latched=19
    2009-11-19 17:36:29.050 spid166   OBJECTSTORE_LOCK_MANAGER (Total)    VM Reserved = 4096 KB    VM Committed = 4096 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 1600 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   OBJECTSTORE_SERVICE_BROKER (Total)    VM Reserved = 0 KB    VM Committed = 0 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 256 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   OBJECTSTORE_SNI_PACKET (Total)    VM Reserved = 0 KB    VM Committed = 0 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 3064 KB   MultiPage Allocator = 48 KB
    2009-11-19 17:36:29.050 spid166   OBJECTSTORE_LBSS (Total)    VM Reserved = 0 KB    VM Committed = 0 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 616 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   USERSTORE_SXC (Total)    VM Reserved = 0 KB    VM Committed = 0 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 240 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   USERSTORE_OBJPERM (Total)    VM Reserved = 0 KB    VM Committed = 0 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 1816 KB   MultiPage Allocator = 24 KB
    2009-11-19 17:36:29.050 spid166   USERSTORE_TOKENPERM (Total)    VM Reserved = 0 KB    VM Committed = 0 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 1912 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   USERSTORE_DBMETADATA (Total)    VM Reserved = 0 KB    VM Committed = 0 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 728 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   USERSTORE_SCHEMAMGR (Total)    VM Reserved = 0 KB    VM Committed = 0 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 5848 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   CACHESTORE_SYSTEMROWSET (Total)    VM Reserved = 0 KB    VM Committed = 0 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 680 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   CACHESTORE_EVENTS (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
    2009-11-19 17:36:29.050 spid166   CACHESTORE_BROKERTO (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
    2009-11-19 17:36:29.050 spid166   CACHESTORE_BROKERREADONLY (Total)    VM Reserved = 0 KB    VM Committed = 0 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 32 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   CACHESTORE_BROKERRSB (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
    2009-11-19 17:36:29.050 spid166   CACHESTORE_BROKERUSERCERTLOOKUP (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
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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 = 112 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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 = 16 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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 = 24 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   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 = 1168 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   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 = 529992 KB   MultiPage Allocator = 7384 KB
    2009-11-19 17:36:29.050 spid166   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 = 14720 KB   MultiPage Allocator = 712 KB
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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 = 11864 KB   MultiPage Allocator = 6888 KB
    2009-11-19 17:36:29.050 spid166   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 = 88 KB   MultiPage Allocator = 64 KB
    2009-11-19 17:36:29.050 spid166   MEMORYCLERK_SQLQERESERVATIONS (Total)    VM Reserved = 0 KB    VM Committed = 0 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 336896 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   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 = 96 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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 = 216 KB   MultiPage Allocator = 16 KB
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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 = 72 KB   MultiPage Allocator = 192 KB
    2009-11-19 17:36:29.050 spid166   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
    2009-11-19 17:36:29.050 spid166   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 = 1496 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   MEMORYCLERK_SQLSTORENG (Total)    VM Reserved = 5760 KB    VM Committed = 5760 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 6088 KB   MultiPage Allocator = 400 KB
    2009-11-19 17:36:29.050 spid166   MEMORYCLERK_SQLUTILITIES (Total)    VM Reserved = 120 KB    VM Committed = 120 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 18600 KB   MultiPage Allocator = 0 KB
    2009-11-19 17:36:29.050 spid166   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 = 3520 KB   MultiPage Allocator = 88 KB
    2009-11-19 17:36:29.050 spid166   MEMORYCLERK_SQLQUERYEXEC (Total)    VM Reserved = 0 KB    VM Committed = 0 KB    AWE Allocated = 0 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 78960 KB   MultiPage Allocator = 416 KB
    2009-11-19 17:36:29.050 spid166   MEMORYCLERK_SQLBUFFERPOOL (Total)    VM Reserved = 1577496 KB    VM Committed = 274968 KB    AWE Allocated = 27721728 KB    SM Reserved = 0 KB    SM Committed = 0 KB   SinglePage Allocator = 0 KB   MultiPage Allocator = 7032 KB
    2009-11-19 17:36:29.050 spid166   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 = 12872 KB   MultiPage Allocator = 2856 KB
    2009-11-19 17:36:29.050 spid166   Memory node Id = 0    VM Reserved = 1617360 KB    VM Committed = 313864 KB    AWE Allocated = 27721728 KB    SinglePage Allocator = 696920 KB   MultiPage Allocator = 26264 KB 
    2009-11-19 17:36:29.050 spid166   Memory Manager    VM Reserved = 1621520 KB   VM Committed = 317872 KB    AWE Allocated = 27721728 KB    Reserved Memory = 1024 KB    Reserved Memory In Use = 0 KB 
    2009-11-19 17:36:28.870 spid153 The client was unable to reuse a session with SPID 153, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
    2009-11-19 17:36:28.870 spid153 错误: 18056,严重性: 20,状态: 27。
    2009-11-19 17:36:28.870 spid59 There is insufficient system memory to run this query.
    2009-11-19 17:36:28.870 spid59 错误: 701,严重性: 17,状态: 193。
    2009-11-19 17:36:28.870 spid129 Error: 18056, Severity: 20, State: 27. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
    2009-11-19 17:36:28.870 spid169 There is insufficient system memory to run this query.
    2009-11-19 17:36:28.870 spid169 错误: 701,严重性: 17,状态: 130。


    目前采取的措施:  把这台服务器上的应用移走了大部分。
    请高手给分析一下,是哪儿的问题,我应该如何处理?
    2009年11月19日 10:57

答案

  • pagefile大小在系统属性里面。你看看可用内存,page/sec是多少。
    不过,你应该看看dmv,看看sql的内存使用具体情况。减少hash join和大的排序。
    想不想时已是想,不如不想都不想。
    2009年11月20日 1:14
    版主

全部回复

  • Is it dedicated sql server? How big the page file is?
    2009年11月19日 16:26
  • 是专用数据库服务器,上面有三个库。 其中两个库访问比较频烦。     另外如何知道页文件大小?
    2009年11月20日 1:00
  • pagefile大小在系统属性里面。你看看可用内存,page/sec是多少。
    不过,你应该看看dmv,看看sql的内存使用具体情况。减少hash join和大的排序。
    想不想时已是想,不如不想都不想。
    2009年11月20日 1:14
    版主
  • 楼上说的是,一般是某个sql出问题了,建议用sql profiler去抓一下

    找到对应的sql 然后做优化。

    基本就ok了。


    family as water
    2009年11月20日 8:01
  • 这种错误信息我也遇到过,但是不确定是否跟你的原因相同。
    1、查看一下发生错误时数据库的可用物理内存情况,如果存在一定数量的可用物理内存,则可能是内存碎片过多所造成的。
    你是否使用了某种自定义的扩展存储过程?这可能会造成周期性的发生内存不足的情况。
    重启数据库服务是否查询就可以正常进行了?
    2009年11月20日 8:16
  • 从移走了部分应用之后 效果明显来看
     应该是移走部分对数据库的查询暂用内存特别大 
       
     应该对业务逻辑进行检查 

      如果没大问题 应该对数据库进行优化

      特别是检查死锁

    2009年11月20日 8:24
  • 在32位系统上,SQL SERVER对AWE内存使用仅限于Data Buffer,那么,当其他内存<非Data Buffer>分配超32位预分配的地址空间时,出现错误就可能了
    建议上64bit,从授权来说,MS是不区分的。从应用来说,是完全没区别的
    Why do not ??

    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com
    2009年11月20日 13:10
  • 另:如果没特别限制<比如SQL LIC升级费用>,建议上SQL 08,它对很多并行处理、调度作了改善
    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com
    2009年11月20日 13:12
  •   你可以看看缓存命中率是否在90%以上;同时查看一下可供进程使用的内存数。
    2009年11月24日 2:09
  • 缓存命中率90%太低了,另外,缓存跟这个问题没有关系。

    另外,profiler早过时了,有dmv和extend event,谁还用那个耗资源的玩意儿。
    想不想时已是想,不如不想都不想。
    2009年11月24日 4:13
    版主
  • I'll not go that far, sql has reason to keep profiler.
    2009年11月24日 4:17
  • 缓存命中率用于表明数据库是否缺少可用内存;从而确定你的系统是否内存可供数据库可用
    2009年11月24日 5:32
  • 内存不足基本可以肯定,而且可能是memory-to-leave的buffer不够了。要么用64位的系统,要么试试多个instances,把应用程序分开在多个instances上
    2009年11月24日 8:40