Memory issues in SQL Server 2005 SP4

  • Hello All,

    We are running SQL Server 2005 SP4 with 16GB of RAM and 4 core CPU on Virtual Machine (VMWare). Set MAX memory to SQL Server @ 12GB, getting below error for every 2 days. Please help me in resolving this.

    Once this issue comes SQL Server will go for non responding state, restart is last destination.

    2014-11-25 19:34:55.410 Server Failed allocate pages: FAIL_PAGE_ALLOCATION 1

    2014-11-25 19:34:55.430 spid1324 Error: 701, Severity: 17, State: 193.

    2014-11-25 19:34:55.430 spid1324 There is insufficient system memory to run this query.

    2014-11-25 19:34:55.430 spid76 Error: 701, Severity: 17, State: 135.

    2014-11-25 19:34:55.430 spid76 There is insufficient system memory to run this query.

    2014-11-25 19:34:55.440 spid77 Error: 701, Severity: 17, State: 42.

    2014-11-25 19:34:55.440 spid77 There is insufficient system memory to run this query.

    2014-11-25 19:34:55.470 spid1308

    Memory Manager

    VM Reserved = 16878712 KB

    VM Committed = 12567256 KB

    AWE Allocated = 0 KB

    Reserved Memory = 1024 KB

    Reserved Memory In Use = 0 KB

    2014-11-25 19:34:55.470 spid1308

    Memory node Id = 0

    VM Reserved = 16872952 KB

    VM Committed = 12561584 KB

    AWE Allocated = 0 KB

    SinglePage Allocator = 11675272 KB

    MultiPage Allocator = 25488 KB

    2014-11-25 19:34:55.470 spid1308

    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 = 109584 KB

    MultiPage Allocator = 3984 KB

    2014-11-25 19:34:55.470 spid1308

    MEMORYCLERK_SQLBUFFERPOOL (Total)

    VM Reserved = 16809984 KB

    VM Committed = 12499456 KB

    AWE Allocated = 0 KB

    SM Reserved = 0 KB

    SM Committed = 0 KB

    SinglePage Allocator = 0 KB

    MultiPage Allocator = 16 KB

    2014-11-25 19:34:55.470 spid1308

    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 = 8 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.470 spid1308

    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 = 600 KB

    MultiPage Allocator = 96 KB

    2014-11-25 19:34:55.470 spid1308

    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 = 152 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.470 spid1308

    MEMORYCLERK_SQLSTORENG (Total)

    VM Reserved = 2752 KB

    VM Committed = 2752 KB

    AWE Allocated = 0 KB

    SM Reserved = 0 KB

    SM Committed = 0 KB

    SinglePage Allocator = 252944 KB

    MultiPage Allocator = 864 KB

    2014-11-25 19:34:55.470 spid1308

    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 = 371792 KB

    MultiPage Allocator = 7584 KB

    2014-11-25 19:34:55.470 spid1308

    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

    2014-11-25 19:34:55.470 spid1308

    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 = 304 KB

    2014-11-25 19:34:55.470 spid1308

    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

    2014-11-25 19:34:55.470 spid1308

    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 = 2056 KB

    MultiPage Allocator = 16 KB

    2014-11-25 19:34:55.470 spid1308

    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

    2014-11-25 19:34:55.470 spid1308

    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

    2014-11-25 19:34:55.470 spid1308

    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 = 24 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.470 spid1308

    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 = 934824 KB

    MultiPage Allocator = 12384 KB

    2014-11-25 19:34:55.470 spid1308

    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 = 48 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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 = 8 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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 = 8694080 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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 = 8 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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 = 16 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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 = 8 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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 = 80 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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

    2014-11-25 19:34:55.480 spid1308

    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 = 128 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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 = 1267512 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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 = 560 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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 = 6080 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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 = 640 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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 = 208 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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 = 64 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    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 = 31208 KB

    MultiPage Allocator = 48 KB

    2014-11-25 19:34:55.480 spid1308

    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 = 272 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308

    OBJECTSTORE_LOCK_MANAGER (Total)

    VM Reserved = 32768 KB

    VM Committed = 32768 KB

    AWE Allocated = 0 KB

    SM Reserved = 0 KB

    SM Committed = 0 KB

    SinglePage Allocator = 2056 KB

    MultiPage Allocator = 0 KB

    2014-11-25 19:34:55.480 spid1308 Buffer Distribution: Stolen=209025 Free=180 Cached=1250384

    Database (clean)=75961 Database (dirty)=449

    I/O=0, Latched=1

    2014-11-25 19:34:55.480 spid1308 Buffer Counts: Committed=1536000 Target=1536000 Hashed=76411

    Internal Reservation=307200 External Reservation=0

    Stolen Potential=-209

    Min Free=140 Visible=1536000

    Available Paging File=19727339520

    2014-11-25 19:34:55.480 spid1308 Procedure Cache: TotalProcs=7 TotalPages=1086764 InUsePages=1086753

    2014-11-25 19:34:55.480 spid1308 Process physical/virtual memory pressure: 1/0 System physical memory pressure: 0

    2014-11-25 19:34:55.480 spid1308 Global Memory Objects:

    Resource=13651

    Locks=260

    SE Schema Mgr=158438

    SQLCache=227

    Replication=2

    ServerGlobal=49

    XPGlobal=2

    Xact=536

    SETLS=4

    DatasetMemObjs=8

    SubpDescPmos=4

    SortTables=2

    2014-11-25 19:34:55.480 spid1308 Query Memory Objects: Grants=0 Waiting=0 Maximum=68529 Available=68529 Limit=68529

    2014-11-25 19:34:55.480 spid1308 Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=72135

    2014-11-25 19:34:55.480 spid1308 Small Query Memory Objects: Grants=0 Waiting=0 Maximum=3606 Available=3606 Limit=3606

    2014-11-25 19:34:55.480 spid1308

    Optimization Queue

    Overall Memory = 10085990400 Target Memory = 1208287232 Last Notification = SHRINK Timeout = 6 Early Termination Factor = 5

    2014-11-25 19:34:55.480 spid1308

    Small Gateway

    Configured Units = 16 Available Units = 16 Acquires = 0

    Waiters = 0 Threshold Factor = 380000 Threshold = 380000

    2014-11-25 19:34:55.480 spid1308

    Medium Gateway

    Configured Units = 4 Available Units = 4 Acquires = 0

    Waiters = 0 Threshold Factor = 12 Threshold = -1

    2014-11-25 19:34:55.480 spid1308

    Big Gateway

    Configured Units = 1 Available Units = 1 Acquires = 0

    Waiters = 0 Threshold Factor = 8 Threshold = -1

    2014-11-25 19:34:55.480 spid1308

    MEMORYBROKER_FOR_CACHE

    Allocations = 1250388

    Rate = -12

    Target Allocations = 882335

    Future Allocations = 0

    Last Notification = SHRINK

    2014-11-25 19:34:55.480 spid1308

    MEMORYBROKER_FOR_STEAL

    Allocations = 209021

    Rate = 0

    Target Allocations = 147496

    Future Allocations = 0

    Last Notification = SHRINK

    2014-11-25 19:34:55.480 spid1308

    MEMORYBROKER_FOR_RESERVE

    Allocations = 0

    Rate = 0

    Target Allocations = 76950

    Future Allocations = 285362

    Last Notification = STABLE

  • Looks nasty! Seems to be addressed in this microsoft link

    http://support.microsoft.com/kb/2001221

    What is running at the time?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply