Memory issues in SQL Server 2005 SP4

Question

Post reply

Memory issues in SQL Server 2005 SP4

mailtovinayaka

SSC-Addicted

Points: 409
More actions
November 25, 2014 at 9:08 pm

#313403

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

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

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

cunningham SSCarpal Tunnel Points: 4595 More actions · Answer 1

Looks nasty! Seems to be addressed in this microsoft link

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

What is running at the time?