none
Error: 17803, Severity: 20, State: 17 - Insufficient memory available..

    Question

  • Hi,
    I'm currently getting Error: 17803, Severity: 20, State: 17 - Insufficient memory available.. on a SQL Server 2000 Standard Edition.


    In the below KBs is mentioned that the well known issue was fixed in the SP4  

    http://support.microsoft.com/kb/815114

    http://support.microsoft.com/kb/323212

    But my SQL Server is already 8.00.2282. In other words SP4. 
    The OS is Win2003 (Windows NT 5.2 - Build 3790: Service Pack 2).
    The HW is RAM 3.3 GB + 8 CPU 3000 Mhz.

    It's a development box where no more than 10 developers work together.

    Any idea of what I could check?
    Thank you very much in advance.
    Regards,
    Marco





    Wednesday, February 17, 2010 4:00 PM

Answers

  • Hi,

    In this case, I suggest you monitor the memory usage first.
    1. To monitor the memory condition, check the following object counters in the System Monitor.
    • Memory: Available Bytes
    • SQL Server: Memory Manager: Total Server Memory (KB)
    The Available Bytes counter indicates how many bytes of memory are currently available for use by processes. Low values for the Available Bytes counter can indicate that there is an overall shortage of memory on the computer. 
    The Total Server Memory (KB) counter indicates how many bytes of memory are used for SQL Server.  If the Total Server Memory (KB) counter is consistently high compared to the amount of physical memory in the computer, it may indicate that more memory is required.
    2. To check the internal memory pressure by SQL Server itself, run the DBCC MEMORYSTATUS command. For more information, please see http://support.microsoft.com/kb/271624

    My suggestion is:
    1. Consider adding more physical memory for your server when the Available Bytes counter keeps low. After adding the sufficient memory, use the /3GB switch to request 3 GB of memory for SQL Server.  However, I don’t suggest you use the /3GB switch under 3.3 GB RAM. Less than 1 GB memory impacts the Operation System.
    2. Adjust memory usage by setting the max server memory option. This option limits the memory size for SQL Server.
    3. If your SQL Server has the large databases and is performing the frequent transactions, consider to upgrade your SQL Server to a new version that supports enabling AWE. SQL Server 2000 Standard doesn’t support this feature to reserve more memory. SQL Server 2000 Standard is limited to 3 GB of physical memory.
    4. Optimize the query. You could rewrite the query, changing the indexes on the tables, or perhaps modifying the database design based on the recommendations from http://msdn.microsoft.com/en-us/library/aa178417(SQL.80).aspx.

    References:
    How to adjust memory usage by using configuration options in SQL Server
    http://support.microsoft.com/kb/321363
    Monitoring Memory Usage
    http://msdn.microsoft.com/en-us/library/aa905152(SQL.80).aspx

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Thursday, February 18, 2010 3:07 AM
    Moderator

All replies

  • What is the entire ErrorLog entry set when the error occurs?  Is the server really 8 CPU's or is it 4 with Hyper Threading Enabled?  If it is hyper threading enabled, start by disabling that, and then see if it goes away.  I'd also probably look at changing max degrees of parallelism if it is still set at the default of 0 as well to 1/2 of the physical processor cores without hyper threading enabled.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Thursday, February 18, 2010 2:14 AM
    Moderator
  • Hi,

    In this case, I suggest you monitor the memory usage first.
    1. To monitor the memory condition, check the following object counters in the System Monitor.
    • Memory: Available Bytes
    • SQL Server: Memory Manager: Total Server Memory (KB)
    The Available Bytes counter indicates how many bytes of memory are currently available for use by processes. Low values for the Available Bytes counter can indicate that there is an overall shortage of memory on the computer. 
    The Total Server Memory (KB) counter indicates how many bytes of memory are used for SQL Server.  If the Total Server Memory (KB) counter is consistently high compared to the amount of physical memory in the computer, it may indicate that more memory is required.
    2. To check the internal memory pressure by SQL Server itself, run the DBCC MEMORYSTATUS command. For more information, please see http://support.microsoft.com/kb/271624

    My suggestion is:
    1. Consider adding more physical memory for your server when the Available Bytes counter keeps low. After adding the sufficient memory, use the /3GB switch to request 3 GB of memory for SQL Server.  However, I don’t suggest you use the /3GB switch under 3.3 GB RAM. Less than 1 GB memory impacts the Operation System.
    2. Adjust memory usage by setting the max server memory option. This option limits the memory size for SQL Server.
    3. If your SQL Server has the large databases and is performing the frequent transactions, consider to upgrade your SQL Server to a new version that supports enabling AWE. SQL Server 2000 Standard doesn’t support this feature to reserve more memory. SQL Server 2000 Standard is limited to 3 GB of physical memory.
    4. Optimize the query. You could rewrite the query, changing the indexes on the tables, or perhaps modifying the database design based on the recommendations from http://msdn.microsoft.com/en-us/library/aa178417(SQL.80).aspx.

    References:
    How to adjust memory usage by using configuration options in SQL Server
    http://support.microsoft.com/kb/321363
    Monitoring Memory Usage
    http://msdn.microsoft.com/en-us/library/aa905152(SQL.80).aspx

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Thursday, February 18, 2010 3:07 AM
    Moderator
  • Hi Jonathan,
    thank you very much for your answer.

    Yes, from 2.30pm Sql Server became unavailavle. no way to connect to the server anyhow. at 3pm we decided to restart the services. after that the server was fine.  from the logs we can see that from 2.30pm to 3pm there was an endless sequence of:

    - Insufficient memory available..
    - Error: 17803, Severity: 20, State: 14

    only around 2.37pm there is a different block of messages that I'm not able to interpretate:

    - LazyWriter: warning, no free buffers found.
    - Buffer Distribution:  Stolen=3302 Free=0 Procedures=50 Inram=201688 Dirty=123 Kept=0 I/O=0, Latched=1669, Other=0
    - Buffer Counts:  Commited=206832 Target=206832 Hashed=203480 InternalReservation=274 ExternalReservation=0 Min Free=1024 Visible= 206832
    - Procedure Cache:  TotalProcs=7 TotalPages=50 InUsePages=50
    - Dynamic Memory Manager:  Stolen=3352 OS Reserved=1048 OS Committed=1026 OS In Use=1022 Query Plan=90 Optimizer=25 General=3249 Utilities=172 Connection=350
    - Global Memory Objects:  Resource=3036 Locks=151 SQLCache=51 Replication=2 LockBytes=2 ServerGlobal=34 Xact=47
    - Query Memory Manager:  Grants=0 Waiting=0 Maximum=101386 Available=101386
    - Error: 17803, Severity: 20, State: 14
    -Insufficient memory available..
    ...
    ...
    - Error: 17803, Severity: 20, State: 14
    - Insufficient memory available..

    and so on..

    the processors are 8 actual Xeon 3Ghz but you are right the max parallelism is 0. I might try to change it to 4.

    I don't know if it could be relevant to say that the external company that is developing their products on this server uses NOLOCK on any query possible.

    Of course the management is asking for a reason for that crash and also if it can happen again.

    Any further help will be more than appreciated.
    Thank you very much indeed for your answer!
    Marco


    The
    Thursday, February 18, 2010 10:08 AM
  • Hi,
    Thank you very much indeed for your detailed explanation. that's a fantastic help! I'll setup the monitor and will execute the tests reccomended. hopefully I'll define the exact reason for that crash.
    Many thanks,
    Marco

    Thursday, February 18, 2010 10:35 AM