Insufficient Memory
-
Hi,
I get this error in my error log.
Error: 17803, Severity: 20, State: 12 Insufficient memory available.
Details of server
Windows 2000 Advanced server(sp4 -Build 2195)
CPU: 8 at 3000 MHz, RAM : 8 GB.
I don't know if \PAE is enabled as I am not able to find the file
sp_configure 'awe enabled' shows the following output (AWE is not enabled)
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
awe enabled 0 1 0 0
sp_configure 'max server memory'
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
max server memory (MB) 4 2147483647 2147483647 2147483647
Meaning it is configured to use only 2 GB of available 8 GB RAM (Hope I am right)
sp_configure output
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 2048 2048
nested triggers 0 1 1 1
network packet size (B) 512 65536 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
If I enable AWE and put \PAE in boot.ini file will the problem be solved
How do I increase the internal buffer size and memory space for the SQL Server process so that I can insert large streams of text data. (sp_configure option??)
Can some one give me a little pointer.
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
To make use of memory more than 4 Gigs you MUST enable AWE in SQL Server and use /PAE switch in the boot.ini
But, this may or may not solve you problems.
17803 errors could be an outcome of buffer pool or MTL memory pressure. IF you are getting "WARNING: Failed to reserve contiguous memory" errors in your error logs, then you have MTL issues and using -g384 or -g512 startup options should help you.
But this would be just a easy workaround, not a solution.
The best way to get rid of the problem is to find out and tune the queries demanding large chunk of memory in one go. (e.g. File uploads or XML operations,etc.)
Here are a few things to try out:
================================
Run a profiler trace when you are experiancing the issue and try to nails the queries causing trouble.
Feed this trace to Index Tuning Wizard to see if it makes any index recommondations.
Best Luck!!
-
You also need to be running SQL Server Enterprise Edition.
Standard Edition only supports 2GB RAM.
--------------------
Colt 45 - the original point and click interface
-
Hi ,
thanks to both of you for your valuable suggestion. I was thinking on that lines. Just got it confirmed from you
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
Hi,
I had the same problem recently. What I found was a couple of spid's that had high memusage figures (> 800Mb). I noticed that the login_time was 3 weeks ago while the last_batch was current.(application not closing connections when not in use). After I Killed the connections the problem went away. all that changing the -g memory-to-reserve parameter might do is prolong the inevitable.
query to check for high memory usage
select sd.name,
sp.dbid,
sp.spid,
sp.memusage *8 /1024 as "mem(mb)", -- number of mem pages * 8 kb / 1024
login_time,
last_batch
from sysprocesses sp inner join sysdatabases sd
on sp.dbid = sd.dbid
order by memusage desc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply