从windows错误日志中先看看sqlserver停机的原因

解决方案 »

  1.   

    select
    a.type,
    SUM(a.virtual_memory_reserved_kb) as vm_reserved,
    sum(a.virtual_memory_committed_kb) as vm_committed,
    SUM(a.awe_allocated_kb) as awe_allcoated,
    SUM(a.shared_memory_reserved_kb) as sm_reserved,
    SUM(a.shared_memory_committed_kb) as sm_commited,
    SUM(multi_pages_kb) as multipage_allocatoer,
    SUM(single_pages_kb) as sinlgepage_allocator
    from sys.dm_os_memory_clerks a
    group by type
    order by 1--先看看内存的使用情况   把结果截图发出来
      

  2.   

    type vm_reserved vm_committed awe_allcoated sm_reserved sm_commited multipage_allocatoer sinlgepage_allocator
    CACHESTORE_BROKERDSH 0 0 0 0 0 0 8
    CACHESTORE_BROKERKEK 0 0 0 0 0 0 8
    CACHESTORE_BROKERREADONLY 0 0 0 0 0 0 32
    CACHESTORE_BROKERRSB 0 0 0 0 0 0 8
    CACHESTORE_BROKERTBLACS 0 0 0 0 0 0 56
    CACHESTORE_BROKERTO 0 0 0 0 0 0 8
    CACHESTORE_BROKERUSERCERTLOOKUP 0 0 0 0 0 0 8
    CACHESTORE_CONVPRI 0 0 0 0 0 0 64
    CACHESTORE_EVENTS 0 0 0 0 0 0 16
    CACHESTORE_FULLTEXTSTOPLIST 0 0 0 0 0 0 32
    CACHESTORE_NOTIF 0 0 0 0 0 0 16
    CACHESTORE_OBJCP 0 0 0 0 0 136 2832
    CACHESTORE_PHDR 0 0 0 0 0 176720 14137384
    CACHESTORE_SQLCP 0 0 0 0 0 1343208 6760952
    CACHESTORE_STACKFRAMES 0 0 0 0 0 16 0
    CACHESTORE_SYSTEMROWSET 0 0 0 0 0 0 1712
    CACHESTORE_TEMPTABLES 0 0 0 0 0 0 16
    CACHESTORE_VIEWDEFINITIONS 0 0 0 0 0 0 16
    CACHESTORE_XMLDBATTRIBUTE 0 0 0 0 0 0 8
    CACHESTORE_XMLDBELEMENT 0 0 0 0 0 0 8
    CACHESTORE_XMLDBTYPE 0 0 0 0 0 0 24
    CACHESTORE_XPROC 0 0 0 0 0 0 88
    MEMORYCLERK_BHF 0 0 0 0 0 0 600
    MEMORYCLERK_FULLTEXT 0 0 0 0 0 0 24
    MEMORYCLERK_FULLTEXT_SHMEM 0 0 0 384 384 0 0
    MEMORYCLERK_HOST 0 0 0 0 0 16 664
    MEMORYCLERK_QSRANGEPREFETCH 0 0 0 0 0 0 128
    MEMORYCLERK_SNI 0 0 0 0 0 32 136
    MEMORYCLERK_SOSNODE 0 0 0 0 0 260024 29160
    MEMORYCLERK_SQLBUFFERPOOL 67190784 65536 63034800 0 0 1944 0
    MEMORYCLERK_SQLCLR 0 0 0 0 0 0 8
    MEMORYCLERK_SQLCLRASSEMBLY 0 0 0 0 0 0 0
    MEMORYCLERK_SQLCONNECTIONPOOL 0 0 0 0 0 0 8384
    MEMORYCLERK_SQLGENERAL 0 0 0 0 0 4240 5880
    MEMORYCLERK_SQLHTTP 0 0 0 0 0 0 8
    MEMORYCLERK_SQLOPTIMIZER 0 0 0 0 0 928 384
    MEMORYCLERK_SQLQERESERVATIONS 0 0 0 0 0 0 0
    MEMORYCLERK_SQLQUERYCOMPILE 0 0 0 0 0 0 0
    MEMORYCLERK_SQLQUERYEXEC 0 0 0 0 0 0 1104
    MEMORYCLERK_SQLQUERYPLAN 0 0 0 0 0 0 0
    MEMORYCLERK_SQLSERVICEBROKER 0 0 0 0 0 544 152
    MEMORYCLERK_SQLSERVICEBROKERTRANSPORT 0 0 0 0 0 0 48
    MEMORYCLERK_SQLSOAP 0 0 0 0 0 0 0
    MEMORYCLERK_SQLSOAPSESSIONSTORE 0 0 0 0 0 0 0
    MEMORYCLERK_SQLSTORENG 6528 6528 0 0 0 5416 9704
    MEMORYCLERK_SQLUTILITIES 240 240 0 0 0 0 120
    MEMORYCLERK_SQLXML 0 0 0 0 0 0 0
    MEMORYCLERK_SQLXP 0 0 0 0 0 0 16
    MEMORYCLERK_TRACE_EVTNOTIF 0 0 0 0 0 0 0
    MEMORYCLERK_XE 0 0 0 0 0 184 128
    MEMORYCLERK_XE_BUFFER 4224 4224 0 0 0 0 0
    OBJECTSTORE_LBSS 0 0 0 0 0 2864 272
    OBJECTSTORE_LOCK_MANAGER 131072 131072 0 0 0 0 140520
    OBJECTSTORE_SECAUDIT_EVENT_BUFFER 0 0 0 0 0 0 16
    OBJECTSTORE_SERVICE_BROKER 0 0 0 0 0 0 424
    OBJECTSTORE_SNI_PACKET 0 0 0 0 0 112 488952
    USERSTORE_DBMETADATA 0 0 0 0 0 0 13448
    USERSTORE_OBJPERM 0 0 0 0 0 0 1360
    USERSTORE_SCHEMAMGR 0 0 0 0 0 9944 89632
    USERSTORE_SXC 0 0 0 0 0 0 48
    USERSTORE_TOKENPERM 0 0 0 0 0 0 544
      

  3.   

    win下任务管理器显示占用的内存不准确。就是那个进程里面的sqlserver.exe 显示并不是指整个sqlserver本身的内存,而只是显示了.exe这个exe的本身。 这个百度一下,网上有很多篇关于这个的说明。内存占用多是非常正常的。 内存占用多也不会引起连接很慢,几乎可以90%以上肯定不是因为内存的占用引起的。我觉得可能性是你的网络问题100M网口 + 交换机?  如果是的话,建议换成千兆口+千兆交换机。。 我就遇到过这事。。也是跟你一样的现象。
      

  4.   

    本帖最后由 TravyLee 于 2014-05-23 15:23:57 编辑
      

  5.   

    DBA_Huangzj版主:
    我是查看的“计算机管理”-“系统工具”-“事件查看器”-“应用程序”里的日志,这个对吧?
    我看里边有个备份,经历了挺长时间的,这之间服务重启过多次。如下图
    还有下面的
    还有一些跟踪不断地启动停止,如下图
      

  6.   

    应该是内存资源耗尽(非Data Buffer部分),设计、开发问题
    就使用1G多内存,应该是32位,且未打开PAE+AWE,那么即使打开PAE,这个一阵子后连接慢或失败也不能解决
    根源在于改进设计、开发代码