Sql server 2005 Memory preasure
Hi,
following is the memory preasure message i found in sqlserver error log
The windows box contains 16 cpus and 4 GB ram.i thinking that there is not enough memory in the box causing memory preasure errors.should i expand the ram to 8 GB?
could you provide me the resolution by looking at the clarification above?
following is the memory preasure message i found in sqlserver error log
2011-04-04 10:30:06.73 Backup Log was backed up. Database: comperio_v21, creation date(time): 2009/05/30(18:13:13), first LSN: 208944:15925:1, last LSN: 208950:6891:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.1\MSSQL\Backup\comperio_v21\TranLogs\comperio_v21_TB_2011_04_04_10_30__01.BAK'}). This is an informational message only. No user action is required.
2011-04-04 10:30:08.66 Backup Log was backed up. Database: comperio_v21_6_4, creation date(time): 2010/07/01(11:17:12), first LSN: 138909:129603:1, last LSN: 138909:129603:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.1\MSSQL\Backup\comperio_v21_6_4\TranLogs\comperio_v21_6_4_TB_2011_04_04_10_30__01.BAK'}). This is an informational message only. No user action is required.
2011-04-04 10:30:09.00 Backup Log was backed up. Database: Pwc, creation date(time): 2009/05/30(18:13:45), first LSN: 84978:169404:1, last LSN: 84978:175536:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.1\MSSQL\Backup\Pwc\TranLogs\Pwc_TB_2011_04_04_10_30__01.BAK'}). This is an informational message only. No user action is required.
2011-04-04 10:30:09.57 Backup Log was backed up. Database: Pwc_4_23, creation date(time): 2010/05/06(12:02:42), first LSN: 84722:222323:1, last LSN: 84722:222323:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.1\MSSQL\Backup\Pwc_4_23\TranLogs\Pwc_4_23_TB_2011_04_04_10_30__01.BAK'}). This is an informational message only. No user action is required.
2011-04-04 11:27:46.84 spid1s AppDomain 2 (PwCAdmin.dbo[runtime].1) is marked for unload due to memory pressure.
2011-04-04 11:27:46.85 spid1s AppDomain 2 (PwCAdmin.dbo[runtime].1) unloaded.
2011-04-04 11:29:00.88 spid55 AppDomain 3 (PwCAdmin.dbo[runtime].2) created.
2011-04-04 11:29:00.93 spid55 Unsafe assembly 'getdiskspace, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil' loaded into appdomain 3 (PwCAdmin.dbo[runtime].2).
I run DBCC memorystatus command on sqlserver 2005 instance which is running on windows 2003 enterprise 32bit sp2
the output is give below.
Memory Manager KB
------------------------------ --------------------
VM Reserved 1755128
VM Committed 1642040
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
------------------------------ --------------------
VM Reserved 1750968
VM Committed 1638032
AWE Allocated 0
MultiPage Allocator 11960
SinglePage Allocator 451880
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 11280
MultiPage Allocator 2480
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 1588480
VM Committed 1588480
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 384
(7 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 728
MultiPage Allocator 88
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 120
VM Committed 120
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 168
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 7104
VM Committed 7104
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2280
MultiPage Allocator 104
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 792
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 138112
VM Committed 25968
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1136
MultiPage Allocator 1248
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 192
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 5320
MultiPage Allocator 7064
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_OBJCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 30808
MultiPage Allocator 192
(7 row(s) affected)
CACHESTORE_SQLCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 364192
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_PHDR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 15200
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 120
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_CLRPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1616
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1896
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_DBMETADATA (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 5136
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1896
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2280
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 200
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 440
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1240
MultiPage Allocator 48
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 256
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4288
MultiPage Allocator 0
(7 row(s) affected)
Buffer Distribution Buffers
------------------------------ -----------
Stolen 2751
Free 4006
Cached 53734
Database (clean) 96547
Database (dirty) 39570
I/O 0
Latched 0
(7 row(s) affected)
Buffer Counts Buffers
------------------------------ --------------------
Committed 196608
Target 196608
Hashed 136117
Stolen Potential 130292
External Reservation 0
Min Free 512
Visible 196608
Available Paging File 357867
(8 row(s) affected)
Procedure Cache Value
------------------------------ -----------
TotalProcs 3365
TotalPages 51307
InUsePages 31
(3 row(s) affected)
Global Memory Objects Buffers
------------------------------ --------------------
Resource 191
Locks 539
XDES 81
SETLS 16
SE Dataset Allocators 32
SubpDesc Allocators 16
SE SchemaManager 236
SQLCache 1140
Replication 2
ServerGlobal 26
XP Global 2
SortTables 2
(12 row(s) affected)
Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 116038
Maximum (Buffers) 116038
Limit 116038
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 122145
(11 row(s) affected)
Small Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 6107
Maximum (Buffers) 6107
Limit 6107
(5 row(s) affected)
Optimization Queue Value
------------------------------ --------------------
Overall Memory 1290829824
Target Memory 802480128
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway Value
------------------------------ --------------------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway Value
------------------------------ --------------------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 12
(5 row(s) affected)
Big Gateway Value
------------------------------ --------------------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 row(s) affected)
MEMORYBROKER_FOR_CACHE Value
-------------------------------- --------------------
Allocations 53735
Rate 0
Target Allocations 148949
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_STEAL Value
-------------------------------- --------------------
Allocations 2745
Rate 0
Target Allocations 97959
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_RESERVE Value
-------------------------------- --------------------
Allocations 0
Rate -8
Target Allocations 130297
Future Allocations 35083
Last Notification 1
(5 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.The windows box contains 16 cpus and 4 GB ram.i thinking that there is not enough memory in the box causing memory preasure errors.should i expand the ram to 8 GB?
could you provide me the resolution by looking at the clarification above?
Let's try that again:
Unless you have enabled AWE and depending on your edition, SQL Server can only use 2GB.
Unless you have enabled AWE and depending on your edition, SQL Server can only use 2GB.
ASKER
The server is not AWE enabled.As per my knowledge we can not enable awe for 4gb of ram as the operation syatem requires 2gb of ram out of 4gb.if it is more then 4gb we can do the awe enabled using 3 gb switch.My question is that the memory pressure due to not sufficient memory in the box?
>>My question is that the memory pressure due to not sufficient memory in the box?<<
Maybe. But my point is that yes you can expand to 8GB, but unless your edition supports it and AWE is enabled, it is not going to do you much good.
Maybe. But my point is that yes you can expand to 8GB, but unless your edition supports it and AWE is enabled, it is not going to do you much good.
ASKER
i think windows 2003 sp2 32 bit enterprise support edition of RAM capacity.
>>it is not going to do you much good.<<
what is your solution for memory preassure by looking at the errorlog?
>>it is not going to do you much good.<<
what is your solution for memory preassure by looking at the errorlog?
ASKER
it is a sqlserver 2005 enterprise edition so that we can do awe enabled.what is your solution for memory preassure by looking at the errorlog?
I am afraid I have no idea.
Do you have hyperthreading turned on? What version SQL Server is? Can you monitor memory at OS level in the time of problem happening again?
Could you also post your min and max memory settings?
ASKER
Min Memory setting is 500mb and max memory setting is 1700mb as sqlserver uses only 2gb of ram
Here's your problem. Your server has 2GB of RAM and SQL Server uses up to 1,7GB leaving 300MB for OS. In this case unloading appdomain is normal behaviour during system memory requirements grows.
ASKER
operation system is already having 2gb of memory from 4gb.out of sql server 2gb we put above memory settings.
Do you have only SQL Server on that machine? Can any other process (e.g. IIS server) require additional memory?
ASKER
no,it has only sqlserver on that machine
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
excellent links
Unless you have enabled AWE SQL Server and depending on your edition you can only use 2GB.