July 15, 2014 at 8:48 am
We've noticed quite a few errors with one of our SQL Servers. It's SQL 2008 (10.0.2531) Standard with 12 GB of physical memory of which 9 GB are dedicated to SQL Server via the max memory setting. The minimum memory setting is set to 0. The errors we're seeing in the SQL error log are as follows:
Error: 17806, Severity: 20, State: 2. SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed
Error: 18056, Severity: 20, State: 29. The client was unable to reuse a session with SPID 90, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Error: 18056, Severity: 20, State: 46. The client was unable to reuse a session with SPID 72, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Error: 18452, Severity: 14, State: 1. Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
Error: 18456, Severity: 14, State: 46. Login failed for user xxxx. Reason: Failed to open the database configured in the login object while revalidating the login on the connection.
Error: 701, Severity: 17, State: 123. There is insufficient system memory in resource pool 'internal' to run this query.
Memory constraints resulted reduced backup/restore buffer sizes. Proceding with 6 buffers of size 64KB.
There was also one memory dump which I've pasted below. I also queried sys.dm_os_process_memory which returned a "1" for the process_virtual_memory_low value. So I'm fairly certain it's low on virtual memory but I'm not sure why or how to fix it. Any ideas are greatly appreciated.
Thanks!
Rebecca
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 5898241
Big Gateway (internal) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -11
Buffer Pool Value
---------------------------------------- ----------
Committed 1178496
Target 1178496
Database 1067337
Dirty 4972
In IO 0
Latched 3
Free 97890
Stolen 13269
Reserved 121779
Visible 182272
Stolen Potential 38110
Limiting Factor 10
Last OOM Factor 0
Page Life Expectancy 2121
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 01
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 01
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 01
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 01
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 72
MultiPage Allocator 01
CACHESTORE_BROKERTBLACS (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 80
MultiPage Allocator 01
CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 152
MultiPage Allocator 01
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 01
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 01
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 240
MultiPage Allocator 01
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 01
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 01
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 01
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4112
MultiPage Allocator 801
CACHESTORE_PHDR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1080
MultiPage Allocator 01
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 480
MultiPage Allocator 2001
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 81
CACHESTORE_STACKFRAMES (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 81
CACHESTORE_STACKFRAMES (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 81
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 241
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 328
MultiPage Allocator 01
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 01
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 01
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 01
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 01
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 01
CACHESTORE_XPROC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 01
Global Memory Objects Pages
---------------------------------------- ----------
Resource 575
Locks 786
XDES 547
SETLS 16
SE Dataset Allocators 32
SubpDesc Allocators 104
SE SchemaManager 799
SE Column Metadata Cache 991
SQLCache 49
Replication 5
ServerGlobal 27
XP Global 2
SortTables 36741
Medium Gateway (internal) Value
---------------------------------------- ----------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -11
Memory Manager KB
---------------------------------------- ----------
VM Reserved 1794268
VM Committed 186440
AWE Allocated 9434824
Reserved Memory 1024
Reserved Memory In Use 01
Memory node Id = 0 KB
---------------------------------------- ----------
VM Reserved 8448
VM Committed 8256
AWE Allocated 0
MultiPage Allocator 6656
SinglePage Allocator 959201
Memory node Id = 1 KB
---------------------------------------- ----------
VM Reserved 1783004
VM Committed 175476
AWE Allocated 9434824
MultiPage Allocator 55960
SinglePage Allocator 959201
Memory node Id = 32 KB
---------------------------------------- ----------
VM Reserved 1728
VM Committed 1672
AWE Allocated 0
MultiPage Allocator 1600
SinglePage Allocator 959201
Memory Pool (internal) Pages
---------------------------------------- ----------
Allocations 135126
Predicted 135841
Private Target 0
Private Limit 0
Total Target 173158
Total Limit 173158
OOM Count 01
Memory Pool Manager Pages
---------------------------------------- ----------
Reserved Current 123137
Reserved Limit 1611691
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- ----------
Allocations 4850
Rate 715
Target Allocations 42882
Future Allocations 0
Overall 146102
Last Notification 11
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- ----------
Allocations 123137
Rate 0
Target Allocations 146102
Future Allocations 32529
Overall 146102
Last Notification 11
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- ----------
Allocations 7139
Rate -47
Target Allocations 44456
Future Allocations 0
Overall 146102
Last Notification 11
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 01
MEMORYCLERK_BHF (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 01
MEMORYCLERK_BHF (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 01
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 01
MEMORYCLERK_HOST (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 56
MultiPage Allocator 01
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 56
MultiPage Allocator 161
MEMORYCLERK_SNI (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 64
MultiPage Allocator 161
MEMORYCLERK_SNI (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 161
MEMORYCLERK_SNI (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 120
MultiPage Allocator 481
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 7768
MultiPage Allocator 55681
MEMORYCLERK_SOSNODE (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 6680
MultiPage Allocator 29921
MEMORYCLERK_SOSNODE (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 15201
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 14464
MultiPage Allocator 100801
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 1565048
VM Committed 98132
AWE Allocated 9434824
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 29281
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- ----------
VM Reserved 133888
VM Committed 1948
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 648
MultiPage Allocator 12721
MEMORYCLERK_SQLCLRASSEMBLY (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 308
SM Committed 308
SinglePage Allocator 0
MultiPage Allocator 01
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 688
MultiPage Allocator 01
MEMORYCLERK_SQLCONNECTIONPOOL (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 592
MultiPage Allocator 01
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1280
MultiPage Allocator 01
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4144
MultiPage Allocator 131921
MEMORYCLERK_SQLGENERAL (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 296
MultiPage Allocator 01
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4440
MultiPage Allocator 131921
MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 01
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1040
MultiPage Allocator 881
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 985096
MultiPage Allocator 01
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 19768
MultiPage Allocator 14561
MEMORYCLERK_SQLQUERYEXEC (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4504
MultiPage Allocator 01
MEMORYCLERK_SQLQUERYEXEC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24272
MultiPage Allocator 14561
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 160
MultiPage Allocator 3441
MEMORYCLERK_SQLSERVICEBROKER (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 01
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 176
MultiPage Allocator 3441
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 01
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- ----------
VM Reserved 10176
VM Committed 10176
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8896
MultiPage Allocator 294881
MEMORYCLERK_SQLSTORENG (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 424
MultiPage Allocator 28161
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------- ----------
VM Reserved 10176
VM Committed 10176
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 9320
MultiPage Allocator 323041
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- ----------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 184
MultiPage Allocator 01
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 01
MEMORYCLERK_XE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 912
MultiPage Allocator 961
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- ----------
VM Reserved 2816
VM Committed 2816
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 01
MEMORYCLERK_XE_BUFFER (node 1) KB
---------------------------------------- ----------
VM Reserved 1408
VM Committed 1408
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 01
MEMORYCLERK_XE_BUFFER (Total) KB
---------------------------------------- ----------
VM Reserved 4224
VM Committed 4224
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 01
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 144
MultiPage Allocator 01
OBJECTSTORE_LBSS (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 128
MultiPage Allocator 01
OBJECTSTORE_LBSS (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 272
MultiPage Allocator 01
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2760
MultiPage Allocator 01
OBJECTSTORE_LOCK_MANAGER (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3504
MultiPage Allocator 01
OBJECTSTORE_LOCK_MANAGER (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 01
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- ----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 6288
MultiPage Allocator 01
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 400
MultiPage Allocator 01
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 880
MultiPage Allocator 561
OBJECTSTORE_SNI_PACKET (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 808
MultiPage Allocator 561
OBJECTSTORE_SNI_PACKET (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 561
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1688
MultiPage Allocator 1681
Optimization Queue (internal) Value
---------------------------------------- ----------
Overall Memory 1196867584
Target Memory 364183552
Last Notification 1
Timeout 6
Early Termination Factor 51
Procedure Cache Value
---------------------------------------- ----------
TotalProcs 23
TotalPages 744
InUsePages 4871
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 490819584
Available Virtual Memory 35287040
Available Paging File 12852187136
Working Set 182255616
Percent of Committed Memory in WS 100
Page Faults 87339038
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 11
Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 2
Waiting 0
Available 6980
Current Max 130117
Future Max 130117
Physical Max 130117
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 01
Small Gateway (internal) Value
---------------------------------------- ----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 2500001
Small Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 6848
Current Max 6848
Future Max 68481
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 600
MultiPage Allocator 01
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4432
MultiPage Allocator 481
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 13208
MultiPage Allocator 11201
USERSTORE_SXC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 256
MultiPage Allocator 01
USERSTORE_SXC (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 264
MultiPage Allocator 01
USERSTORE_SXC (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 01
USERSTORE_SXC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 528
MultiPage Allocator 01
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3920
MultiPage Allocator 01
USERSTORE_TOKENPERM (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 328
MultiPage Allocator 01
USERSTORE_TOKENPERM (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4248
MultiPage Allocator 01
July 15, 2014 at 10:33 am
You seem to have sufficient available physical memory when the error was reported. Also, the error message reports that you are failing to acquire 576KB of contiguous memory. This might be a fragmentation issue.
Memory constraints resulted reduced backup/restore buffer sizes. Proceding with 6 buffers of size 64KB. <-- This error also commonly is a victim of memory fragmentation when sufficient contiguous memory is not available for the backup buffers.
Does this problem rectify by itself or do you have to restart the SQL service to rectify the issue? Is this a 64 or 32-bit instance?
Could you check if your largest free block size is dropping over a period of time? The query in the post below should help you retrieve that value:
http://troubleshootingsql.com/2009/12/30/how-to-find-the-largest-free-block-in-the-mtl/
Could you also point out which of the common non-bufferpool memory consumers are active in your environment?
Reference: http://troubleshootingsql.com/2009/12/30/mtl-consumers/
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: TroubleshootingSQL
Twitter: @banerjeeamit
July 15, 2014 at 11:07 am
Hi Amit!
Here are the answers to your questions.
It's a 32-bit machine.
The total max contiguous block size in KB is 3456.
Also, the errors seem sporadic. We haven't found any patterns to the errors. The department using it or nor their users have not reported any issues yet. We have not rebooted it recently.
Common non-bufferpool memory consumers :
2. I could not find any stored procedures using XML or anything related to XML.
3. No extended stored procedure usage that I can detect.
4. Query Plans which are larger than 8 KB in size:
bucketidrefcountsusecountssize_in_bytesmemory_object_addresscacheobjtypeobjtype
6820 2 1163840 0x2108C038Compiled PlanProc
7595 2 1237568 0x20C74038Compiled PlanProc
5508 2 140960 0x4C858038Compiled PlanProc
9846 2 124576 0x22C22038Compiled PlanAdhoc1
684 1 216384 0x22C62038Parse TreeView
5. SQLCLR usage: None that I can find
6. Backups using larger MAXTRANSFERSIZE parameter: None
7. Network Packet Size higher than 8192 bytes: None
However, there is a lot of replication going on with this server. It's both a publisher and a subscriber (not my decision).
Thanks!
Rebecca
July 15, 2014 at 11:51 am
This is definitely a MTL fragmentation issue. Finding out the culprit is going to be difficult without tracking it. If possible, run the query to gather the largest free block maybe every 2-4 hours and see if it steadily drops. During the time of the drop, you could correlate it with the activity occurring on the server to see if the same activity induces a drop consistently.
Since this is a 32-bit server, you do not have many options. Your MTL region is limited in a 32-bit SQL instance. The worst case scenario would be to use the -g parameter and try seeing if -g384 helps increase the frequency with which the error occurs. What is the current frequency of the issue?
Some proactive suggestions to ensure MTL consumers are moved out the SQL Server process space:
1. See if the linked server providers can be run out of process
2. Check if the larger query plans can be optimized
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: TroubleshootingSQL
Twitter: @banerjeeamit
July 15, 2014 at 11:53 am
Additional troubleshooting queries:
If the above doesn't help, then a more invasive way to identify the culprit would be to capture XPerf traces: http://troubleshootingsql.com/2011/05/31/tools-tips-and-tricks-12-xperf-memory-usage-and-much-more/
Or capture a Debug Diag memory leak trace:
http://troubleshootingsql.com/2011/05/27/tools-tips-and-tricks-11-debug-diag-and-memory-leaks/
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: TroubleshootingSQL
Twitter: @banerjeeamit
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply