Thanks again for answer
I understand your way to set autogrow "grow by Megabytes", I think I will do this in future too. But this does not solve the reason why the tempDB starts to grow last summer. Is it possible that raising user count and a raising count of Data overall brings this 8GB server to a memory bottleleck?
To less physical memory seems to cause more use of TempDB. But on the other hand the TempDB Space is almost unused (free).
I have also seen that TempDB growing only occurs at busineshours, in the night wíth less people working, no growing of TempDB took place.
So I used DBCC MEMORYSTATUS for the first time. See below. At the moment I try to interpret the output by using http://support.microsoft.com/kb/907877.
Thanks for Help.
Peter
Memory Manager KB
------------------------------ --------------------
VM Reserved 14879948
VM Committed 6929756
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 Zeile(n) betroffen)
Memory node Id = 0 KB
------------------------------ --------------------
VM Reserved 14874188
VM Committed 6924084
AWE Allocated 0
MultiPage Allocator 103472
SinglePage Allocator 2807152
(5 Zeile(n) betroffen)
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 29424
MultiPage Allocator 4968
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 8413184
VM Committed 6695888
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLOPTIMIZER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1896
MultiPage Allocator 112
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLUTILITIES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 392
MultiPage Allocator 43240
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 5120
VM Committed 5120
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3536
MultiPage Allocator 664
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3616
MultiPage Allocator 0
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLCLR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 6311168
VM Committed 80456
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 920
MultiPage Allocator 20256
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLCLRASSEMBLY (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 3868
VM Committed 3868
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 0
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 304
(7 Zeile(n) betroffen)
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 Zeile(n) betroffen)
MEMORYCLERK_SNI (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 352
MultiPage Allocator 16
(7 Zeile(n) betroffen)
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 Zeile(n) betroffen)
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 Zeile(n) betroffen)
MEMORYCLERK_BHF (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 784
MultiPage Allocator 0
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLQERESERVATIONS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8448
MultiPage Allocator 0
(7 Zeile(n) betroffen)
MEMORYCLERK_HOST (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 248
MultiPage Allocator 208
(7 Zeile(n) betroffen)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 25344
MultiPage Allocator 13296
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 64
(7 Zeile(n) betroffen)
CACHESTORE_OBJCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 62992
MultiPage Allocator 648
(7 Zeile(n) betroffen)
CACHESTORE_SQLCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2543232
MultiPage Allocator 14792
(7 Zeile(n) betroffen)
CACHESTORE_PHDR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 13368
MultiPage Allocator 304
(7 Zeile(n) betroffen)
CACHESTORE_XPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0
(7 Zeile(n) betroffen)
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 Zeile(n) betroffen)
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 Zeile(n) betroffen)
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 Zeile(n) betroffen)
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 Zeile(n) betroffen)
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 Zeile(n) betroffen)
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 Zeile(n) betroffen)
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 Zeile(n) betroffen)
CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 616
MultiPage Allocator 0
(7 Zeile(n) betroffen)
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 Zeile(n) betroffen)
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 Zeile(n) betroffen)
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 Zeile(n) betroffen)
CACHESTORE_BROKERRSB (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_BROKERREADONLY (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 272
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_BROKERTO (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 Zeile(n) betroffen)
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 Zeile(n) betroffen)
CACHESTORE_CLRPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 40
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_SYSTEMROWSET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3400
MultiPage Allocator 0
(7 Zeile(n) betroffen)
USERSTORE_SCHEMAMGR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6992
MultiPage Allocator 0
(7 Zeile(n) betroffen)
USERSTORE_DBMETADATA (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6968
MultiPage Allocator 0
(7 Zeile(n) betroffen)
USERSTORE_TOKENPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72152
MultiPage Allocator 0
(7 Zeile(n) betroffen)
USERSTORE_OBJPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 912
MultiPage Allocator 0
(7 Zeile(n) betroffen)
USERSTORE_SXC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1120
MultiPage Allocator 0
(7 Zeile(n) betroffen)
OBJECTSTORE_LBSS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 192
MultiPage Allocator 3792
(7 Zeile(n) betroffen)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6496
MultiPage Allocator 608
(7 Zeile(n) betroffen)
OBJECTSTORE_SERVICE_BROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 336
MultiPage Allocator 0
(7 Zeile(n) betroffen)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 32768
VM Committed 32768
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 21032
MultiPage Allocator 0
(7 Zeile(n) betroffen)
Buffer Distribution Buffers
------------------------------ -----------
Stolen 8349
Free 1458
Cached 342544
Database (clean) 447082
Database (dirty) 23405
I/O 0
Latched 4
(7 Zeile(n) betroffen)
Buffer Counts Buffers
------------------------------ --------------------
Committed 822842
Target 835157
Hashed 470491
Stolen Potential 643287
External Reservation 1144
Min Free 520
Visible 835157
Available Paging File 1518639
(8 Zeile(n) betroffen)
Procedure Cache Value
------------------------------ -----------
TotalProcs 33471
TotalPages 329427
InUsePages 3922
(3 Zeile(n) betroffen)
Global Memory Objects Buffers
------------------------------ --------------------
Resource 390
Locks 2632
XDES 151
SETLS 8
SE Dataset Allocators 16
SubpDesc Allocators 8
SE SchemaManager 870
SQLCache 3497
Replication 2
ServerGlobal 60
XP Global 2
SortTables 2
(12 Zeile(n) betroffen)
Query Memory Objects Value
------------------------------ -----------
Grants 2
Waiting 0
Available (Buffers) 400646
Maximum (Buffers) 401830
Limit 401830
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 414630
(11 Zeile(n) betroffen)
Small Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 12800
Maximum (Buffers) 12800
Limit 12800
(5 Zeile(n) betroffen)
Optimization Queue Value
------------------------------ --------------------
Overall Memory 5483814912
Target Memory 2434809856
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 Zeile(n) betroffen)
Small Gateway Value
------------------------------ --------------------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
(6 Zeile(n) betroffen)
Medium Gateway Value
------------------------------ --------------------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 12
(5 Zeile(n) betroffen)
Big Gateway Value
------------------------------ --------------------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 Zeile(n) betroffen)
MEMORYBROKER_FOR_CACHE Value
-------------------------------- --------------------
Allocations 342385
Rate 104
Target Allocations 631074
Future Allocations 0
Last Notification 1
(5 Zeile(n) betroffen)
MEMORYBROKER_FOR_STEAL Value
-------------------------------- --------------------
Allocations 8482
Rate 151
Target Allocations 297218
Future Allocations 0
Last Notification 1
(5 Zeile(n) betroffen)
MEMORYBROKER_FOR_RESERVE Value
-------------------------------- --------------------
Allocations 1032
Rate 1029
Target Allocations 442277
Future Allocations 153692
Last Notification 1
(5 Zeile(n) betroffen)
Die DBCC-Ausführung wurde abgeschlossen. Falls DBCC Fehlermeldungen ausgegeben hat, wenden Sie sich an den Systemadministrator.