February 17, 2006 at 2:56 pm
I'm still getting to know the database that is now in my care, but here is what I know: I've got 3.6GB RAM allocated to SQL Server and it's running through a decent Pentium Xeon processor.
The total of all the databases on disk is around 10 GB. The main consumer is a Powerbuilder application with around 100 users doing data entry and some reporting. We also have Merge replication to a very low traffic website.
Now, one of the developers has been trying copy a DTS package from another server both by "save as" and from saving the DTS to disk and then trying to open it. The following error is in the logs every time (DTS just gives an unspecified error):
2006-02-17 16:17:58.32 spid332 WARNING: Failed to reserve contiguous memory of Size= 5373952.
2006-02-17 16:17:58.33 spid332 Buffer Distribution: Stolen=15430 Free=304 Procedures=94353
Inram=0 Dirty=11631 Kept=0
I/O=0, Latched=1336, Other=84882
2006-02-17 16:17:58.33 spid332 Buffer Counts: Commited=207936 Target=207936 Hashed=97849
InternalReservation=1832 ExternalReservation=25 Min Free=456 Visible= 207936
2006-02-17 16:17:58.33 spid332 Procedure Cache: TotalProcs=15197 TotalPages=94353 InUsePages=46419
2006-02-17 16:17:58.33 spid332 Dynamic Memory Manager: Stolen=109760 OS Reserved=1096
OS Committed=1074
OS In Use=1040
Query Plan=93700 Optimizer=98
General=9899
Utilities=47 Connection=3317
2006-02-17 16:17:58.33 spid332 Global Memory Objects: Resource=3886 Locks=169
SQLCache=5049 Replication=2
LockBytes=2 ServerGlobal=29
Xact=203
2006-02-17 16:17:58.33 spid332 Query Memory Manager: Grants=1 Waiting=0 Maximum=109635 Available=109586
2006-02-17 16:17:58.35 spid332 Error: 17803, Severity: 20, State: 12
2006-02-17 16:17:58.35 spid332 Insufficient memory available..
We've tried it with another DTS package and that also had the same issue. Do I simply need more memory? This seems like a new problem (as of today)
February 17, 2006 at 3:04 pm
try running DBCC FREEPROCCACHE and see that helps..
what does the dbcc memorystatus report w.r.t to MemtoLeave..
does it happen after restarting the service too?
wat is the sql server build version?
-Krishnan
February 17, 2006 at 3:39 pm
DBCC FREEPROCCACHE didn't help. As much as I'd like to, I can't restart SQL Server on Production without more justification.
We are on SP4 for both SQL Server and Win2K Server
DBCC MEMORYSTATUS yields:
Buffer Distribution Buffers
------------------------------ -----------
Stolen 4919
Free 59719
Procedures 11324
Inram 0
Dirty 13072
Kept 0
I/O 0
Latched 1311
Other 117591
(9 row(s) affected)
Buffer Counts Buffers
------------------------------ -----------
Commited 207936
Target 207936
Hashed 131974
InternalReservation 1824
ExternalReservation 0
Min Free 128
Visible 207936
(7 row(s) affected)
Procedure Cache Value
------------------------------ -----------
TotalProcs 2896
TotalPages 11324
InUsePages 6921
(3 row(s) affected)
Dynamic Memory Manager Buffers
------------------------------ -----------
Stolen 16243
OS Reserved 1008
OS Committed 986
OS In Use 982
General 5084
QueryPlan 10788
Optimizer 0
Utilities 46
Connection 775
(9 row(s) affected)
Global Memory Objects Buffers
------------------------------ -----------
Resource 3886
Locks 110
XDES 142
SQLCache 432
Replication 2
LockBytes 2
ServerGlobal 28
(7 row(s) affected)
Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 147060
Maximum (Buffers) 147060
(4 row(s) affected)
Optimization Queue Value
------------------------------ -----------
Optimizing 0
Waiting 0
Available 16
Maximum 16
(4 row(s) affected)
I don't see MemtoLeave. What is it?
February 17, 2006 at 3:44 pm
Found the KB article reference (http://support.microsoft.com/kb/271624/en-us) in another thread. Don't fully understand it yet, still reading.
February 17, 2006 at 3:59 pm
Consists of a single contiguous region of addresses within the SQL Server process space that is set aside at startup. This area is static (the size cannot change) and is left unallocated by SQL Server for the use by components in the SQL Server process space, such as extended procedures, COM/OLE Automation objects, and linked servers. Memory allocations by SQL Server larger than 8 KB are also made from the MemToLeave area. The default is 256MB and can be increased using -g paramter ...
do u see any memory pressure on the box?
what is the page life expectanct perf counter value ?
-Krishnan
February 17, 2006 at 9:48 pm
Are you sure 3.6GB is allocated to SQLServer? Or does the box have 3.6GB but SQLServer is only taking up to 2GB. Simply looking at "taskmgr" to see is not always reliable.
Are both /3gb and /pae in the boot.ini file on the server???
February 18, 2006 at 9:10 am
IS this Std or Ent SQL Server? Standard can only use 2GB.
I might also specify an amount for sQL Server rather than the dynamic memory management. Is this a dedicated SQL Server or does something else run on it?
February 21, 2006 at 8:57 am
i am running enterprise edition with quad processors and 4 GB RAM. The Dynamic settings are set to max at 3008MB which I can probably increase a bit since this is now a dedicated SQL box (I'm forcing everything else off of it.) Oddly enough, task manager shows it using only 1,836,760K
I've also noted that "Boost SQL Server Priority on Windows" is not checked, nor is "Reserve Physical Memory for SQL Server"
The boot.ini listing:
[boot loader]
timeout=0
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" /fastdetect
February 21, 2006 at 12:44 pm
To see how much ram sql server consumes, use the performance counters sql server provides.
The /3gb is missing.
What edition of windows 2000 server is it? Standard, advanced ...
February 21, 2006 at 3:29 pm
Windows 2000 Server (Not advanced) Task Manager shows it using 4 processors and 4 GB RAM (with 1.6GB available right now)
I'm having a hard time checking anything in Perfmon because it seems to be having problems popluating the dropdowns for coutner choices. I'm using Remote Desktop to run it.
February 22, 2006 at 1:25 am
Since it is a windows 2000 standard edition, programs cannot allocate more than 2 gb.
February 22, 2006 at 1:43 pm
Thank you so much for the help.
I've just confirmed that I'm running Win 2k Server Standard and my Page Life Expectancy is around 170. So I've definitely got some memory issues.
As for the DTS package that started me on my memory managemnt explorations, it was able to be saved early in the morning when I had fewer users on.
How I defeated the Perfmon problem: Perfmon still didn't work for me even logged directly into the console. I'd get great stats once every minute or so, which essentially meant I'd only get a sample when SQL wasn't too busy to give them to Perfmon. I used master..sysperfinfo to get the stats I needed and found out what was happening between Perfmon samples. If SQL can't even spare resources to deliver stats to perfmon, I've got some issues...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply